Remove Files From tempdb

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.

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.

27 thoughts on “Remove Files From tempdb

    1. Hi Erin,

      I thankfully did not run into this issue on MSSQL 2016. I even tried hammering tempdb with a few sessions of temp table load query loops in my test system and it let me drop the data and secondary (against nature) tempdb log files.

  1. Thank you for this tweek to fix such tempdb issue.
    (It’s about time the SQLServer team fixes this for tempdb)

  2. So only way the issue can be resolved is by starting SQL Server with minimal configuration in single user mode. In production, outage is required and some time we don’t get outage for 2-3 weeks.

    So it is not easy fix.

    1. Correct, the fix requires an outage and so it may not be “easy” for some customers. HOWEVER, this should be an *extremely rare* situation – it’s not often you have to REMOVE files from tempdb, or any database for that matter, but particularly tempdb. If your files become too large – for example if your tempdb files are properly pre-sized and they grow because of some bad queries that spill in tempdb, etc., – then you can SHRINK the files to get them back the appropriate size. But in this situation, I mistakenly ran a query that ADDED files and pre-sized them and filled up a drive.

      1. Thanks Erin. Isn’t it good practice to fill up a drive dedicated for Tempdb, so as there will be no need to auto grow the files as they already fill the capacity of the drive?

        1. Hey Leon-
          I think that if it’s a dedicated drive that can be a best practice. What I don’t love about doing that is that you really need to understand how much your workload uses tempdb and how large you need to tempdb to be (so you can size the drive and then the files), and then you have to hope that your workload doesn’t change. Meaning…what if you get a new application or a developer makes a change that causes more use of tempdb? Now you need to expand that drive, or add more tempdb files elsewhere. Just something to consider!
          Erin

  3. The documentation notes that “tempdb is configured at the smallest possible size.” So small that not all the files were there— It means all files are not visible. Could you please elaborate….

    1. Hi-

      Are you asking me to elaborate on what Microsoft means by “tempdb is configured at the smallest possible size?”. If so, based on the *one* test I’ve done (in resolving this issue), it appears that SQL Server just makes the initial file for tempdb visible when it starts up. Note that the files still existed out on disk. After I had removed the extra files from tempdb, they were still on disk, but at that point I could delete them manually. I hope that answers your question (Microsoft doesn’t supply any additional documentation on what it means when “tempdb is configured at the smallest possible size”).

      Erin

  4. Erin: you are always great.

    Here pls let me know is there any update on tempdb behavior change in 2012 why it creates work file for each tempdb file..

    1. Hi-

      I don’t understand the scenario you’re describing – where one work file is created for each tempdb file. SQL Server will create workfiles during query execution sometimes when it needs to store information in tempdb, is that what you mean? If so, I’ve never seen it create one workfile for each tempdb file that exists.

      If you’re asking about recommendations for the number of tempdb data files that you need, I recommend Paul’s post:

      https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

      Erin

      1. (Just came across this page after running into that issue last night)
        I think what he’s asking is if you ever found out what MS had changed (and not documented) that was causing this issue. You said “I haven’t found any documentation from Microsoft about this…yet…”, and that’s probably what he’s asking about – it’s what I was going to ask.

  5. This has bit me so many times, but I think I had a consistent solution. I’m almost positive that it was from SQL 2012 onward, and I’m trying to re-engineer the scenario on a couple of test machines (One is SQL 2012 and one is SQL 2016) but haven’t quite gotten it to work. From what I could engineer I think it’s fixed in SQL 2016.

    What I *think* works in SQL 2012 is this:
    1 – Run the shrink with EMPTYFILE, get an error for workpage
    2 – Attempt the REMOVE FILE, get an error that the file can’t be removed since it’s not empty
    If I remember correctly, historically the REMOVE FILE didn’t actually remove the file at the time of the command, it simply removed it from the catalog so when SQL started up the next time the file wasn’t created. But it needed the EMPTYFILE to be run at least once to mark it as empty.

    This is only with TempDB because of two things:
    1 – The worktable issue is only in TempDB, and usually at the end of the file; in my experience it usually can’t even be moved from a normal shrink operation.
    2 – TempDB data files are created on SQL startup, so removing the files from the catalog is the mechanism.

    After the REMOVE FILE a simple SQL restart would prevent those files from being created, but they would still be on disk and need to be manually removed.

    1. A WORKFILE is internally created in tempdb, you don’t have the ability to remove them. They’ll be deleted when the process using them is finished, or when you restart the instance.

  6. I just had a similar issue on one of my SQL boxes and solved it by:
    1. Setting all the secondary tempdb data files to 1 MB in initial size and disabled autogrowth on them.
    2. Restarted SQL service.
    3. Then I was able to remove the secondary temdb data files.

    Hope it helps someone, have a nice day!

    1. This actually worked for me aswell, i had to remove one file because of contention issues ( more files than cpu’s).
      Thanks!

  7. Hi All:
    After reviewing posts regarding how to remove TEMPDB Files. I came up with following, and it worked for me like a charm with out doing all the fancy-shmancy stuff.

    Logical name= temp5
    In query editor simply did the following:

    USE [tempdb]
    GO
    DBCC SHRINKFILE (N’temp5′ , EMPTYFILE)
    GO

    USE master
    ALTER DATABASE [tempdb] REMOVE FILE [temp5]
    GO

  8. I just ran into this again. It’s with a new server I’m using to mimic a PROD server for testing and it has a lot of files. After creating the extra files I needed I realized I had +1 file from the server spinup and tried to remove it via

    ALTER DATABASE tempdb REMOVE FILE temp2

    and got the same error. I ended up trying a different old trick – I changed the size to 1MB

    ALTER DATABASE tempdb MODIFY FILE (NAME = ‘temp2’, SIZE = 1)

    and then was able to successfully remove it.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.