As a SQL Server DBA, you may often come across situations where you need to add or remove TempDB files. Adding TempDB files is a common task, but what if you accidentally added a file and now need to remove it? In this blog post, we will discuss how to remove a TempDB file using T-SQL.

Recently, I received a question from a fellow DBA who had accidentally added an additional TempDB file to their setup. The challenge was that they were not allowed to use SQL Server Management Studio (SSMS) and had to remove the file using T-SQL only.

Here is the script that was accidentally executed to add the additional data file:

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

To reverse this operation and delete the file, the DBA can execute the following script:

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

By running this script, the additional TempDB file will be successfully removed from the SQL Server instance.

It’s important to note that removing a TempDB file should be done with caution, as it can impact the performance and stability of the SQL Server instance. It is recommended to have a backup of the database before making any changes to the TempDB configuration.

If you’re interested in learning more about TempDB, here are a few additional blog posts that you may find helpful:

• 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

Remember, it’s always important to be cautious when making changes to your SQL Server configuration. Understanding how to add and remove TempDB files can help you effectively manage your database environment.