I made a mistake with a script today. I created three new tempdb files sized at 10GB each that filled up a hard drive.
Luckily it was in one of my own testing VMs, so it wasn’t awful. Fixing it, however, was a fun one.
**NOTE: All work was done in a test environment. Proceed with caution if you’re running these commands in Production and make sure you understand the ramifications.
In order to remove a file from a database in SQL Server, it has to be empty. For each file I wanted to remove I needed to run:
USE [tempdb]; GO DBCC SHRINKFILE (logicalname, EMPTYFILE); GO
However, every time I tried to run this command for any file, I would get a message like this:
DBCC SHRINKFILE: Page 4:130 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 “logicalname” to other places to complete the emptyfile operation.
This error came up for each file, even if I restarted the instance and did nothing, and even if I restarted it in single-user mode.
Then I found some posts about clearing the procedure cache, and the session cache, so I cleared everything…go big or go home right? Remember, I’m working in a local test environment so this isn’t a big deal.
DBCC DROPCLEANBUFFERS GO DBCC FREEPROCCACHE GO DBCC FREESESSIONCACHE GO DBCC FREESYSTEMCACHE ( 'ALL') GO
If I tried to empty the file after that, it still failed.
**Note: In talking with Jonathan after the fact, he said he’s seen this before, where every file in tempdb has a workfile in it that you cannot remove. He thinks the behavior started with SQL Server 2012. I haven’t found any documentation from Microsoft about this…yet…
Now I was getting annoyed (mostly with myself for this mistake in the first place). Finally, I tried started SQL Server with minimal configuration, using -f, and connected with sqlcmd. The documentation notes that “tempdb is configured at the smallest possible size.” So small that not all the files were there! I couldn’t run the DBCC SHRINKFILE command because the additional files weren’t available. Perfect, as then I could just remove them:
ALTER DATABASE [tempdb] REMOVE FILE [logicalname] GO
I ran the ALTER DATABASE [tempdb] REMOVE FILE for each of the three files I added, shut down the instance, removed -f, and restarted. The files were removed! However, they were still sitting out on the drive, but because they were no longer in use I could delete them. Space reclaimed, time for some chocolate.
Update April 21, 2020: Andy Mallon has a clever alternative to starting SQL Server with -f, check out his post Fixing tempdb: Growing, shrinking, and removing data files.