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.
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.
- What is the Initial Size of TempDB? – Interview Question of the Week #120
- Moving TempDB to New Drive – Interview Question of the Week #077
- SQL SERVER – Script to Find and Monitoring TempDB Space Usage
- SQL SERVER – How to Start SQL Server Service Without tempdb?Â
- SQL SERVER – Error Msg 5042, Level 16 – The File ‘tempdev02’ Cannot be Removed Because it is Not Empty
- SQL SERVER – Ideal TempDB FileGrowth Value
- SQL SERVER – Reducing Page Contention on TempDB
- SQL SERVER – T-SQL Script to Find Details About TempDB
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
3 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
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.
Super Solution Dimitry !!! I just used it to remove 7 superfluous tempdb Files on a small SQL server (by the way, who had the … idea to create one tempdb file per core) – all worked fine wit your script !!!