I made a mistake with a script today. I created three new tempdb files sized at 10GB each that filled up a hard drive.

Whoops.

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.