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
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | USE [tempdb]; DECLARE @NumFilesToDrop TINYINT, @FileName NVARCHAR(128), @SQL NVARCHAR(500); SET @NumFilesToDrop = 0; /*specify how many data files you want dropped */ DECLARE DropTempDBFiles CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT [name] FROM sys.[database_files] WHERE [type] = 0 ORDER BY [file_id] DESC; OPEN DropTempDBFiles; FETCH NEXT FROM DropTempDBFiles INTO @FileName; WHILE @@FETCH_STATUS = 0 AND @NumFilesToDrop > 0 BEGIN /*empty file*/ SET @SQL = N'DBCC SHRINKFILE (N''' + @FileName + N''' , EMPTYFILE);'; EXEC (@SQL); /*delete file*/ SET @SQL = N'ALTER DATABASE [tempdb] REMOVE FILE [' + @FileName + N'];'; EXEC (@SQL); /*another one bites the dust*/ SET @NumFilesToDrop -= 1; IF @NumFilesToDrop <= 0 BEGIN BREAK; END FETCH NEXT FROM DropTempDBFiles INTO @FileName; END; CLOSE DropTempDBFiles; DEALLOCATE DropTempDBFiles; |
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:
- Paste it in query editor.
- Update the value of
@NumFilesToDropto the number of files you want to drop. - Execute it.
The output will be similar to this:
The result sets from the DBCC SHRINKFILE command.
And, in the Messages tab, you’ll se the output of the drop commands.
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.
- Create a SQL Server Agent job.
- Add the script as a step in the newly created job.
- Update the value of
@NumFilesToDropto the number of files you want to drop. - Create a new schedule for the job that uses the “Start automatically when SQL Server Agent starts” type.
- During the maintenance window stop the SQL Server service – this will automatically stop the SQL Server Agent service for that instance as well
- 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).
- The job will get triggered upon instance startup.
- 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.
1 comment
[…] Vlad Drumea doesn’t want to restart SQL Server: […]