My SQL Server 2008 R2 database size is huge. Is it safe to shrink?
Regards, Melwin Lawrance
asked Sep 19, 2012 at 11:30 AM in Default
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.
answered Sep 19, 2012 at 12:10 PM
Grant Fritchey ♦♦
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.
answered Sep 20, 2012 at 01:21 PM