As a general practice don’t shrink any databases. Every once in a while there is a need to do so. I had one of those needs today.

My disk is full

Thanks to a cumulative effect of a misconfigured SQL install, incorrect location of TempDB and the server pagefile combined with a user running a huge query joining 20+ tables with no where clause we had a drive run out of disk space. Unfortunately this caused another process to break which needed to grow the transaction log on another database.

I contacted the business owner immediately explaining how we needed to move TempDB to another drive and restart SQL (in fact I’d already performed the alter statement to move TempDB off to it’s own LUN where it should have been all along). The business owner stated that we could not restart SQL until after the evening so we were left in a situation where certain things were failing.

Why not shrink TempDB?

That was my feeling. TempDB was 18GB larger than it had ever needed to be so I figured I could just shrink the data file (there was just one) and reclaim 50% of the difference to keep things chugging along until the restart. I attempted to perform the shrink on the file and it did nothing. I checked TempDB and nothing was using it, but still the shrink failed.

Interesting problem. Argenis Fernandez (blog|twitter) just joined my team and he came over to try and help. He suggested running DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS given that the user impact would be minimal given the servers usage patterns. 

After running the DBCC commands I was able to perform the shrink, clean up the space and keep everything running.

Corruption – that’s why

Curious as to why running those DBCC commands allowed me to shrink the file I posted to #sqlhelp on Twitter asking why.

Pretty much immediately I got a reply from Paul Randal (blog|twitter) that made my stomach churn:

Paul and Aaron Bertrand (blog|twitter) sent me links to a Microsoft KB article that clearly explains this.

From Paul I bring you the KB in a nutshell: unless you quiesce SQL Server, a TempDB data file shrink can cause corruption.

This post brought to you by yet another reason not to perform a shrink.