Get Every Last Drop with EMPTYFILE

As I was working on a recent tempdb blog post, I encountered an error when trying to remove data files. Let’s look into the issue you may have removing data files and the solution to get those files cleaned up.

There’s Always One

I had multiple data files in this test environment and wanted to drop that file count down to 1.

I was in the tempdb database properties and thought I’d remove the files from there. I highlighted each file and clicked the Remove button to remove the files. After a few short seconds and a few quick clicks I had the file list looking like this:

All looked good to me until I clicked OK and got presented with an error message.

The file ‘temp3’ cannot be removed because it is not empty.

That shouldn’t have been too big of a surprise that at least one file was in use but how do we empty temp3 so that it can be deleted?

Running on Empty

The answer is to shrink the file and use the EMPTYFILE parameter. EMPTYFILE can be used to “empty” the specified file by moving its data into another file existing within the same filegroup.

Let’s give that a shot. We’ll run DBCC SHRINKFILE for our problem file (temp3) and include EMPTYFILE:

USE [tempdb]
GO
DBCC SHRINKFILE (N'temp3', EMPTYFILE)
GO

No problems there. Our results say no pages are in use. Let’s try dropping the file one more time and do so with this script:

USE [tempdb]
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp3]
GO

Perfect. We’ve torn it all down and can build it back up again with a blog post on adding files.

You’re Just What I Needed

EMPTYFILE probably won’t be something you’ll need too often but it proves useful in these situations.

Thanks for reading!