(somewhat) Social
« Central Auditing Of SQL Permissions Scripts | Main | Presenting For The PASS PowerShell Virtual Chapter »
Wednesday
May182011

Don’t Shrink TempDB!

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:

Dont Shrink Tempdb

 

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.

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (3)

Big point is, you were between a rock and a hard place. Given your situation, I'd have done the same. Can you post a followup and let us know whether shrinking TempDB did indeed cause corruption?

May 20, 2011 | Unregistered CommenterDavid Taylor

I was actually fortunate in that it did not cause any corruption for me. I don't know what the odds are of that actually happening, but I'll consider myself lucky anyhow.

May 25, 2011 | Registered CommenterNic

very well,All things in their being are good for something.-Cartier Classic watch ukExperience without learning is better than learning without experience.

Comments for this entry have been disabled. Additional comments may not be added to this entry at this time.