I like Halloween so I find myself getting mentally ready each year about a couple of months prior. My mind turns to horrors and I must warn wary travelers in DBA-land about an evil I see all too often – shrinking a database.

I know this has been done to death but I still see it. Gather around the fire as I tell you the ills of shrinking database files.

Why is Shrinking a SQL Server Database A Bad Idea?

Bad Advice From Experts

Bad advice is all over the internet. Each year it becomes harder and harder to sort away the noise and bad advice from the valuable good information. It especially rattles my cage when I see otherwise respectable DBAs shrinking databases on the regular. This reminds me of how in the 1800’s and early 1900’s doctors actually prescribed and recommended smoking as a cure to…wait for it…asthma.

At best shrinking a database is a wasted operation. At worse it is harmful because it causes heavy fragmentation. Yes, you can reindex after you shrink, but that does not make it an acceptable action. Your databases need space to grow and making them autogrow every time you need more space is a recipe for poor performance. Databases need space – they are functional and not ornamental. Supposedly they hold mission critical data to the business – let’s treat them like they are valuable.

The nightmare of shrinking a database is not a symptom of DBA madness. It turns out it is the cause…

Data Files

Shrinking the data files (mdf, ndf) is a bad idea and should be reserved for emergency scenarios. Getting to the point where shrinking the database files is a consideration on an actively used OLTP system is a problem in and of itself. It should never get to this point. This is your red flag that somebody dropped the ball on capacity planning or there is a bug allowing all this data in.

DBAs typically track database sizes and growth rates so they can make good decisions with regards to capacity planning. 

Transaction Log Files

Shrinking the log file (only 1 as it is a sequential write) should be reserved until after careful consideration. It is better to find out why the transaction log is so large and get the right amount of disk space to allow for database operations. Sometimes a long running transaction can cause abnormal growth. In cases like this it can potentially makes sense to shrink the transaction log to an appropriate level (not as small as we can). Otherwise if typical database operations require more space then additional disk space must be provisioned to allow for it. 

Remember that when SQL Server has to ask the Operating System for more disk space to grow its files that this is one of the most expensive things SQL Server must do.

Shrinking database files is almost never a solution to any problem you face. Also, shrinking a database is not something to be done without careful consideration. Besides, if you have a database in full recovery mode then you need to be taking backups of the transaction log so it does not endlessly grow.

Yes this Applies to tempdb Too

Tempdb is sometimes the target of a shrink. Please don’t do this. This is truly a pointless activity. If tempdb really is inflated beyond a reasonable limit then rebuild tempdb. Shrinking tempdb may cause transactions to fail or get stuck and will introduce a level a poor performance you will not like.

Autoshink

Microsoft has a KB on this which is worth a read: Considerations for the “autogrow” and “autoshrink” settings in SQL Server

It is hard to believe that autoshrink is still an option available to configure a database and build maintenance plans. This is just as bad as the default power setting of Windows Server. Your laptop can barely run well when on balanced power setting let alone a hardcore RDBMS!

Sigh – yet is is still there. If Paul Randal couldn’t get rid of autoshrink then what hope do we have that it will ever happen?

If you don’t trust this and want to argue then please read these articles from experts who prove it. Test it out for yourself.

• What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE?
• Stop Shrinking Your Database Files. Seriously. Now. – the gold is in the comments when people actually try to defend their addiction to shrink a database
• Why you should not shrink your data files

When Is It Acceptable to Shrink a Database?

There are exceptions but they are strictly just that – exceptions. It is critically important that you appreciate that the medicine you are administering (shrinking a database) is actually causing harm.

There are only a precious few scenarios that I can see a valid argument potentially made for shrinking a database:

1. Cleanup work
   1. Some kind of bad data gets in your database by the boatload and you need to remove it. This is a rare situation.
   2. This is also not something that should happen on a regular basis. If you are frequently shrinking a database to handle cleanup then you must know this is a bad practice and should be stopped. You are not treating the disease but rather fueling the symptoms you seek to treat.
2. Archiving data
   1. If you have an archive policy where you periodically remove tons of data in the database to archive.
   2. That data better never make it back into the database. This is for archival…not just moving data.
3. Absolute show stopping nightmare.
   1. I have consulted for clients who insisted we shrink the database on a regular basis. They would argue that a full disk makes the database unavailable and that we have to shrink the database files to reclaim space.
   2. While it is true that a full disk is super bad, this is the wrong remedy. It you absolutely must shrink your database then there must be an action item for capacity planning right now! This cannot be allowed to continuously happen.
   3. After all – you are monitoring your disks, right?

Hopefully you heed this warning and do not succumb to the temptation that is shrink.

If you need a laugh please check out my SQL Memes!