Home » Script to delete extra tempdb data files

Script to delete extra tempdb data files

by Vlad Drumea
1 comment

This is a brief post containing a script you can use to delete extra tempdb data files and avoid the “file not empty” (Msg 5042) error.

I’ve occasionally ran into situations where an instance is configured with more than the recommended number of tempdb data files.

If you’re dealing with more than a couple of extra files it can get annoying, especially on a busy instance.

Even more so when you might hit this error:

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

The script

You can also find it in my SQL Server Scripts GitHub repo.

What it does

It gets a list of all the data files in tempdb ordered by file_id descending, iterates through that list and empties and drops the first x number of files.
Where x is the value provided to the @NumFilesToDrop parameter.

How to use it

On a “quiet” instance

If you’re dealing with this on an instance that isn’t being heavily used:

  1. Paste it in query editor.
  2. Update the value of @NumFilesToDrop to the number of files you want to drop.
  3. Execute it.

The output will be similar to this:

The result sets from the DBCC SHRINKFILE command.

result set 1: DbId|FileId|CurrentSize|MinimumSize|UsedPages|EstimatedPages 2|13|65536|65536|0|0 result set 2: DbId|FileId|CurrentSize|MinimumSize|UsedPages|EstimatedPages 2|12|65536|65536|0|0 result set 3: DbId|FileId|CurrentSize|MinimumSize|UsedPages|EstimatedPages 2|11|65536|65536|0|0 result set 4: DbId|FileId|CurrentSize|MinimumSize|UsedPages|EstimatedPages 2|10|65536|65536|0|0 delete extra tempdb data files Msg 5042, Level 16, State 1 The file cannot be removed because it is not empty Vlad Drumea VladDBA SQL Server DBA

And, in the Messages tab, you’ll se the output of the drop commands.

 (1 row affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. The file 'temp12' has been removed. (1 row affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. The file 'temp11' has been removed. (1 row affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. The file 'temp10' has been removed. (1 row affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. The file 'temp9' has been removed. delete extra tempdb data files Msg 5042, Level 16, State 1 The file cannot be removed because it is not empty Vlad Drumea VladDBA SQL Server DBA

If, for some reason, the files refuse to be emptied and dropped, you can use resort to the next method.

On a heavily used/production instance

If you want to delete extra tempdb data files on a busy production instance, you’ll need to do a bit of planing beforehand since you’ll need to sync with a maintenance window.

  1. Create a SQL Server Agent job.
  2. Add the script as a step in the newly created job.
  3. Update the value of @NumFilesToDrop to the number of files you want to drop.
  4. Create a new schedule for the job that uses the “Start automatically when SQL Server Agent starts” type.
  5. During the maintenance window stop the SQL Server service – this will automatically stop the SQL Server Agent service for that instance as well
  6. When both services have stopped, start the SQL Server Agent service – this will automatically start the SQL Server service also (I do this to not waste time by manually starting SQL Server first and then the Agent).
  7. The job will get triggered upon instance startup.
  8. Delete the job. Don’t forget this point, since if it gets triggered again it will delete tempdb data files that you actually wanted to keep.

If you want to get creative or don’t have SQL Server Agent installed for that instance, you can turn the script into a startup stored procedure.
But, again, don’t forget to delete the stored procedure after the instance restarted and the extra tempdb data files were deleted.

Conclusion

Extra tempdb data files are a hassle to get rid of sometimes, but they don’t have to be.

You may also like

1 comment

Dropping Extra tempdb Files – Curated SQL March 12, 2024 - 14:00

[…] Vlad Drumea doesn’t want to restart SQL Server: […]

Reply

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.