SQL: Shrinking Files and Auto Shrink in SQL Server - Just Say No

I first started using SQL Server back in 1992 with version 4.2 I was running a software house and was hoping to use SQL Server as the back end of our updated application. 4.2 wasn’t up to it. Versions 6 and 6.5 were closer but I still thought they needed far too much administration for me to be comfortable deploying them to most customer sites. Version 7 though, changed the game. Rather than having to manage devices (which was painful), we now had just standard operating system files, and even had options for automatically growing and shrinking the files.

Growing automatically was a big deal. Running out of disk space within SQL Server 6x devices was really painful and commonplace. Automatically shrinking was an interesting option that we tried on some sites.

In building version 7, Microsoft made a conscious effort to work out if there was a way to automatically set anything that a DBA normally needed to set themselves. Version 7 was the first version of SQL Server that we really felt comfortable deploying to customers who had no onsite IT people. We deployed it, and the sites just went quiet.

Awesome image by Kristina FlourFor a software house (ISV), quiet is good.

We tended not to use Auto Shrink. Manual shrink worked but we barely ever used it anyway and that didn’t change through later versions.

However, Paul Randal noted that in 2005, a bunch of things made shrinking much, much slower. We started hearing more and more horror stories about shrinking.

To give you an example, I was at a client site a year back, and they’d decided to shrink a 2TB database back to 1.3TB. They started the shrink and within an hour, it said it was 64% complete. However, a week later (yes a whole week), it was 62.8% complete. They had contacted us because they thought it was never going to complete. Worse, they were now seeing deadlocks that didn’t normally happen. The deadlocks stopped if they stopped the shrink operation.

This is a horrible outcome for them. And worse, even if it had ever completed, it would have so messed up the internal fragmentation within the database that they would have needed to rebuild all the indexes anyway.

What we did instead was to create a new database and migrate the data over. It took 2 hours and was super-clean. We did it in the right order to avoid the need for index rebuilds later.

Shrinking a SQL Server database isn’t a good option right now. Auto Shrink is almost always an even worse choice.

Migrate the data to another database, or rebuild the data onto a new filegroup if that’s an option.

2018-11-05