Why Can't I Shrink TempDB?

, 2019-01-14 (first published: )

I know, I know....#OMGShrinkingIsBadDontEverDoThatOrElseEtcEtcEtc

We all know that there are simply times you have to shrink some files.  There are risks - blocking, significant I/O, fragmentation, and more - all of which mean you should not shrink a file willy-nilly without considering the impacts...but sometimes you are having a production issue and don't have any choice.  Similarly, sometimes you are in Dev/Test and it is simply more practical to shrink a file than to add drive space or re-architect the full solution.

In many cases it comes down to that unfortunate reality that there's a way the book/class does it, and another way we have to do it "in the field."


Of course you also have to remember Law #9842 of being a DBA - all database systems are Production to someone.  It may be a developer or a QA team rather than an end user, but it is still PROD to them!)


In this story, TempDB DATA files were using almost all of the space on the drive, meaning TempLog couldn't grow, but the DATA files were mostly empty by the time it escalated to me.

Even though the files were mostly empty, my attempts to shrink them were throwing an error and the files were not shrinking:


Msg 5054, Level 16, State 1, Line 1
Could not cleanup worktable IAM chains to allow shrink or remove file operation.  Please try again when tempdb is idle.



Googling the message led me to one of my top five authoritative sources, Paul Randal of SQLskills.com (@PaulRandal/blog).  The relevant blog post is https://www.sqlskills.com/blogs/paul/shrinking-tempdb-longer-prohibited/

While my error message isn't featured in the body of the blog post, it *is* in the final comment and reply in the article.  By that information, it appears that this error reflects that certain system structures are on pages that can't be moved without system restart, which means TempDB can't be manually shrunk on this instance w/o SQL service restart.

(Paul does describe in his article the old-school fear that "shrinking TempDB leads to corruption" and how his extensive experience leads him and Microsoft to now believe that is not true *on modern versions of SQL Server (2005+)*)


Hope this helps!





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads