To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question

Question

We're freeing lots of space in a SQL Server 2008 R2 database -a Loooot! enough to care about- (we're dropping lots of unnecessary data). but the database file conserves its file size. we want to regain it.

I've heard lots of times that using DBCC SHRINKDATABASE reduces the performance of your DBs -As far as I know, because "...A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree" [MSDN]

So I'm planning to use DBCC SHRINKDATABASE and then rebuild our indexes.

There is another performance reason to not use DBCC SHRINKDATABASE to regain disk space?

Answer 1

From Tibor Karaszi's Blog:

So what is the problem? Why shouldn't I shrink database files on a regular basis? Have a look at below list and then you can determine for yourself whether or not you want to shrink database files regularly:

• Each page moved will be logged to the transaction log. Say you have a database with 50GB used space (data and indexes pages), and the shrink will shuffle 40 GB towards the beginning of the file. The log file will for this shrink operation need 40GB, likely auto-grow to that size (if you don't have 40GB free space in the log file already). The following log backup wll be 40GB in size, plus the "regular" log records. This doesn't seem to happen if the database is in simple recovery mode, possibly beacuse CHECKPOINT will prune the log regurarly during the shrink. (Applies to shrinking of data files.)

• After the shrink, as users add rows etc in the database, the file has to grow again. Growing a database file is an expensive operation, it takes time and it also hurts performance (lots of resource usage). Also, some modifications will be blocked until the grow operation has finished. (Applies to shrinking of both data and log files.)

• SQL Server 2005 and later: As of SQL Server 2005, we have "instant file initialization" which means that database files can be created and also grow very fast since Windows doesn't "zero out" the data in the database file. Instant file initialization is only available for data files, not log files. Also, instance file initialization requires that the service account for the SQL Server service has the SE_MANAGE_VOLUME_NAME windows privilige, which can be assigned using the Perform Volume Maintenance Tasks security policy. This is by default only granted to Administrators.

• There are situations where autogrow doesn't "catch up" with the space usage requirements. This will result in an error message from SQL Server when the modification is performed, returned to the client application: error 1105 if data is full and 9002 if log is full. (Applies to shrinking of both data and log files.)

• Moving datapages around will fragment your database. Say you rebuild your indexes (which will requires free space in the database), and then shrink the database. The shrink will essentially undo the index rebuild, leaving you with fragmented indexes. Don't believe me? This is easy to test for yourself.

• What if you do it the other way around, shrink first, then rebuild? Well, the rebuld need free space in the database for the largest index that you rebuild, and it is likely you have a large table with a clustered index. A friend of mine had a 4GB used space db, where almost all space was one 4GB table. He did a shrink and then rebuild, where the re-build immediately "bumped up" the db size to 8GB. (Applies to shrinking of data files.)

• Heavy shrinking and growing of database files will fragment your file system, which will further hurt performance. (Applies to shrinking of both data and log files.)

• Repeatedly srinking and subsequent growing of transaction log files will typically result in many virtual log files which can cause long startup time for the database. This can manifest itself as long startup time for a database, long restore time, delays for transaction replication etc. Check out this blog post for more information.