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)
