asked
Database size is increasing rapidly.Urgent help
I have a client who's DB size is growing rapidly. As its a busy Client, they have to compress the DB on daily basis. Is there any way,we can auto compress the DB?
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
You can implement Compression(Page, Row) for reducing the amount of storage needed. But, the decision should be taken based on the application, as compression leads to more CPU Load.
Appreciate for the prompt assistance. How can we achieve compression at page,row level? sorry i have no idea how to implement the same. Major concern of client is to not compress on daily basis. Our task is to stop the db growth so rapidly. Not sure yet whether .mdf or .ldf is growing.
Before you choose a solution you should be clear what the problem is. This is a SQL Server database? What do you mean when you say they compress the DB? I'm not aware of a compress feature other that permanently turning on the page or row level compression. If you mean they keep shrinking the database and it keeps growing back, is it actually getting bigger and bigger over, say a month, or is it just growing back to the same size it was when they shrank it? If it needs to be a certain size, it's better to accept that and just leave it at that size.
Agreed, the first time I started running out of space, it took me a while to realize it was my transaction log and not the database itself. I would want to know how they are determining that the database size is growing rapidly.
Excellent point. It could just be the transaction log. Maybe it's in full recovery model and they are not backing up the log.
Appreciate all for sharing your thoughts on the same. I investigated the issue and found that .mdf was 14 GB and out of which 1 gb was showing unused. I encountered they have issue with .ldf and same growing on weekly basis. Due to less space issue on their server,client IT used to shrink it every alternate day and wanted to fix it. I found database was in simple recovery mode. DB growth Settings was set to grow 10%. So set the DB growth settings to grow by 10 mb, kept the .mdf and .ldf files on different drive so that they will not have space issue so frequently. Created a Job for shrinking .ldf to 1Mb on weekly basis.
Shrinking the ldf file over and over is problematic though. If they have transactions of such a size and duration that the log needs to be a certain size, it's just going to have to grow to that size again. Growing will cause things to slow down while transactions wait for the growth to complete. Better to set it to the right size and let it go. Also, growing the database by such a tiny amount means lots of fragmentation at the disk level. You want auto grow to occur very infrequently, not very frequently. So you set it grow by an amount that's big enough, not tiny.
Grant said: "... actual growth of the transaction log is something very slow and incremental ..." If you find that your transaction log growth is not very slow and incremental, then that probably means it was not given enough space to start with.
If the required size of the transaction log is causing the drive to fill, then answer isn't trying to shrink the log over and over. It's just going to grow back to that size again (except for one-off crazy, errors, stuff like that). So, if that size is too big for the drive, your choices are; get a bigger drive (sorry, but it's just going to grow again) or reduce the size and/or number of transactions (figure out why your transaction log growth is so disproportional to what you think it ought to be, determine where that comes from and make adjustments accordingly). Then, set the size to what it needs to be. In most systems I've managed, actual growth of the transaction log is something very slow and incremental, only as the load on a system grew did we see transaction log growth. If the system was relatively steady state in regards to the number of users and the number and size of transactions, the log usually stayed very constant (even if data grew). It shouldn't be something you wrestle with and shrink over and over again.
Agreed. Barring the occasional accident or out of control data load. 100%
@Grant: So what do you recommend? How much should we keep the auto growth settings? What best we can do in such scenario's?
18 People are following this question.
