I have multiple data files created for Tempdb database let’s say tempdev2. Now when I try to remove additional mdf files from filegroup, I got below issue. SQL Server says..
Msg 5042, Level 16, State 1, Line 1 The file 'tempdev2' cannot be removed because it is not empty.
Cause : The reason for such behavior is there are some activities going on that is using files created your TempDB database. The general behavior is to restart the SQL server that will create your TempDB files and reset it. But the only problem with it is it will down the other environments / databases.
USE [tempdb] GO DBCC SHRINKFILE('tempdev2', EMPTYFILE) GO ALTER DATABASE [tempdb] REMOVE FILE [tempdev2] GO
I hope this will help you!
Like this post and post comments if you like it.