asked
SQL Database Shrink
Hi Friends, My SQL Server 2008 R2 database size is huge. Is it safe to shrink? Regards, Melwin Lawrance
Safe to shrink? Sure. Wise to shrink? Usually not. There are two issues with shrinking databases. First, and probably most important, it leads to serious fragmentation of your data files. Because of this, it's very strongly recommended against shrinking a database. Second, if the database has grown to a certain size due to the data being inserted into it, it probably needs to be that size in order to maintain your systems. Shrinking it is just likely to lead to the database growing again. Are there circumstances where shrinking might make sense? Sure. Let's say you're in full recovery and for some reason log backups failed and your log file grew outrageously, then a one-time shrink on that file makes sense. Or, a user made a mistake and created a million rows or something on the database that you've subsequently cleaned up, then shrinking might make sense. But unless you're dealing with this type of egregious example, no, I would not recommend shrinking the database.
If your database is in FULL recovery, but you're only running log backups once a day, that's your first problem. Log backups are meant to be run frequently to provide a recovery mechanism to a point in time, not simply to act as a flush for committed transactions. I link to a number of articles on this, and other backup topics, in this blog post:
http://www.scarydba.com/2012/01/11/database-backups-things-you-need-to-do-now/ Getting your log backups under control will help you deal with log size. Shrinking your database every single night for months, you have seriously fragmented files. All you're getting done is cleaning out a little free space at the end of the files and then reallocating it again during the day as more data gets added. I'd stop now. Whatever database size you have, that's what you have. If you need to store 500gb of data, you can't store less. If you need 2tb, you can't store less. So, if the database is getting big, you need more drive space or more drives. If the log is getting big, you need to adjust the frequency of the log backups. And stop shrinking your database over & over. More...
I can't see what you're running, but it's worth noting. If you're rebuilding all indexes, then the next two steps, update statistics and reorganize indexes are wasted. Updating the stats, unless you tell it otherwise is a sampled operation, but rebuilding the index is a scanned operation, meaning, it's much more accurate. So by updating the stats after rebulding the indexes, you're making the stats worse. Reorganize is just a way to defrag lightly fragmented indexes by logically rearranging them. But if that index has just been rebuilt, there's nothing for this step to do. There are sophisticated processes that can filter which indexes get rebuilt and which get regorged, but you need to be careful about stats updates around these processes for reasons outlined above.
Just one question - why are you doing those jobs all at once?
Thanks Grant for your response. I have a maintenance plan running everyday night at 11 which has the below mentioned tasks are running in this order. 1. Check DB Integrity 2. Back Up Db Full 3. Back up Db transaction log 4. Shrink Db 5. Rebuild index 6. Update statistics 7. Reorganize Index 8. Clean up history 9. maintenace clean up task ( which will dlete the .bak and .trn files which are older than 3 days. It was running with out any problems for the last 3 months, suddenly the it started failing due to space issues on the drive. if i check the maintenace history it failing in Rebuild task due to less space. Now my db and log files are in huge size. I have shrinked the log files couple of times for to get some space, but i am worried with the size of the db, because it may get fail tonight due to the big size of the db. what should i do now? Please advice. Regards, Melwin
Rebuild and Reorg may grow your database files and/or log files (log growth in reorg and datafile growth due to the fill factor leaving some empty space on each page and thus requiring more pages). As others have said already - I'd advice against shrinking the database regularly. But if you need to shrink your files, I'd do that as the very last step of the maintenance. Otherwise you'll shrink your files, followed by a maintenance task which grows the file(s) again.
11 People are following this question.
