I recently ran into an interesting problem while attempting to remove approximately half of the TempDB data files configured in a testing environment. As you might expect, there are various SQL Server tasks that are performed infrequently by a DBA, and this is a good example of one of them. Most environments have usually been misconfigured with too few TempDB data files or wrongly sized usually resulting in the classic allocation page contention problems (which is explained by this excellent SQLSkills article “The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention“), but this particular environment had been temporarily over provisioned with disks and TempDB data files (for testing purposes).

So the plan was to remove each TempDB file one by one, restart the instance (if required) and decommission those spare disks so that they can be returned and reallocated elsewhere in the environment. In order to remove each data file we would:

1. Empty each file at a time (using DBCC SHRINKFILE).
2. Remove each file upon empty.

Now there was a time (prior to SQL Server 2005) when we were told to tread very very carefully when shrinking TempDB files, and doing so could result in corruption. This perception remains with some of the longer serving SQL Server professionals (I question myself frequently) but if we take a look at KB307487 article “How to shrink the tempdb database in SQL Server” we can see that it is now safe to do so -although (as the Knowledge Base article states) certain scenarios can cause the shrink operation to fail.

So I ran the following code for each TempDB data file:

DBCC SHRINKFILE (tempdb_15, EMPTYFILE)
go
ALTER DATABASE [tempdb] REMOVE FILE [tempdb_15]
GO

The code succeeded for the first few files, right up to TempDB data file 12, where I hit the following error:

DBCC SHRINKFILE: Page 14:56 could not be moved
because it is a work table page.
Msg 2555, Level 16, State 1, Line 1
Cannot move all contents of file "tempdb_12" to
other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdb_12' cannot be removed
because it is not empty.

As odd as this error was (especially since the SQL instance was not currently “in use”) I decided to bounce it but after restarting the instance was again greeted with the same error message! After some very quick Google-Fu I came across an old MSDN Forum question “DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.” I ruled out some of the responses but came across the response by Mike Rose:

“I realize this is an old thread but I have found that in most cases work tables are related to Query Plans.

Try issuing the following commands and the shrinking the tempdb:

DBCC FREESYSTEMCACHE (‘ALL’)

DBCC FREEPROCCACHE

there will be some performance hit for this as SQL will have to recreate its Query Plans, but it should allow you to shrink you TEMPDB.

Hope this helps,

M Rose”

 
Now my initial thoughts were that this response was clearly nonsense since I had just restarted the SQL Service for the instance (thereby flushing the SQL caches), but hey what the hell, why not give it a whirl….

…and success! It worked.

Apart from the flushing of the caches, the outcome was even more strange to me for another reason. Upon reboot TempDB is “recreated” upon startup_*1 and I would have expected that fact alone to have fixed the problem but from the behaviour I had experienced, something had persisted across instance restart.
_{*1 you may want to read this interesting post by Jonathan Kehayias “Does tempdb Get Recreated From model at Startup?“}

Also of interest to me was whether running FREEPROCCACHE and FREESYSTEMCACHE was overkill so when the opportunity arose I attempted first to try only clearing the systemcache specific to TempDB through:

DBCC FREESYSTEMCACHE ('tempdb')

…and then tried clearing temporary tables and table variables through:

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables')

…and then by using both together. Sadly these did not seem to work.

Clearing system caches on a Production SQL Server instance is never a good thing and should be avoided, but at least the next time I run into this problem I have (what appears) to be a work around to the problem I ran into and at least the requirement to remove TempDB data files should be a rare occurrence for most of us!

I confess that I am left with a few unanswered questions and clearly what I experienced does make me doubt the results somewhat (and my sanity) and there were further tests that I would like to perform at some point. So if you run into this problem yourself I encourage you to try this (disclaimer: your fault if you destroy your SQL Server 😉 ) and I would be delighted if you leave feedback of what works for you (and what doesn’t)!