As a SQL Server DBA you would know that sometimes it is possible to make mistakes with the database. Few of the time it can be corrected. Out of them, some are easy and some are difficult. I was running out of space on my VM and I started hunting for a bigger file on my machine. It was not difficult to find and recall that I added some files to the TempDB database while doing some testing. Here is the command to get details about the database file, which gave me Error Msg 5042.
sp_helpdb tempdb
When I tried to remove the file via the UI and got below error.
Also tried via T-SQL below
USE [tempdb] GO ALTER DATABASE [tempdb] REMOVE FILE [tempdev02] GO
And got the same error
Msg 5042, Level 16, State 1, Line 8
The file ‘tempdev02’ cannot be removed because it is not empty.
This was not a production environment so it was easy for me to restart, but looking at the message, it looks like it would work, if I make the file empty. So I ran below command
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev02', EMPTYFILE) GO
It was successful and then I was able to remove the file.
Reference: Pinal Dave (https://blog.sqlauthority.com)
