SQL SERVER – How to Remove TempDB File?

In the last 11 years of blogging, I have never received a question which I have received yesterday. The question which I often receive is about how to add TempDB files in SQL Server but I have never received a question about how to remove a TempDB file.

One of the DBA accidently added an additional TempDB file to their setup and had no idea what to do next to go back to the original situation. Here is the screenshot of the files which he accidentally added into TempDB. The challenge which he was facing that he was not allowed to use SQL Server Management Studio (SSMS) and he has to remove this file with T-SQL only.

Here is the script which he had accidently ran to add the additional data file tempdev1.

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'C:\tempdev1.ndf')
GO

What he wanted to do is to reverse the operation by deleting the file. Here is another script which he can execute and delete the file which he accidentally added.

SQL SERVER - How to Remove TempDB File? tempdbremove-800x537

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

Well, that’s it.

Here are few additional blog posts which are related to TempDB.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Move Database Files for a Mirrored Database Without Breaking Mirroring
Next Post
SQL SERVER – How to Auto Start SQL Followed by a Crash?

Related Posts

2 Comments. Leave new

  • I have found that you get the following error most of the time.

    Msg 5042, Level 16, State 1, Line 22
    The file ‘tempdev2’ cannot be removed because it is not empty.

    For me the following extra lines solved the problem. !note: This was needed for a test server and the EMPTYFILE option was not enough to get it to work:

    USE tempdb;
    GO
    DBCC DROPCLEANBUFFERS
    GO
    DBCC FREEPROCCACHE
    GO
    DBCC FREESESSIONCACHE
    GO
    DBCC FREESYSTEMCACHE ( ‘ALL’)
    GO
    DBCC SHRINKFILE (‘tempdev2’, 1024)
    GO
    DBCC SHRINKFILE(‘tempdev2’, EMPTYFILE)
    GO
    USE master;
    GO
    ALTER DATABASE tempdb
    REMOVE FILE tempdev2;

    hope it helps

    Greeting Dimitry

    Reply
  • Hi Pinal,
    I have created by mistake an additional data file in the incorrect file group, I need to either remove the file completely or move the file to the default file group. Looks like changing files from one file group to another is not possible?

    The data file is empty so I tried removing the file using alter DB remove file, when I run DBCC showfilestats, the file is gone, but when I check SYS.master_files, I can see still the entry of the file I want removed.
    Is there a way to remove it completely, even deleting it physically from the server as I need to recover the space?

    Hope you can help me.

    Kind regards,
    Jose.

    Reply

Leave a Reply

Menu