06 February,2013 by Jack Vamvas
To remove a secondary sql transaction log file is accomplished by the ALTER DATABASE REMOVE FILE command . You may have needed to add a second transaction log file to deal with a very large transaction . Now the transaction is finished , you’re looking to delete the secondary transaction log file.
USE [mydatabase] GO ALTER DATABASE [mydatabase] REMOVE FILE [mydatabase_Log_2] GO
But under certain circumstances this error message may appear
Msg 5042, Level 16, State 1, Line 1
The file 'mydatabase_Log_2' cannot be removed because it is not empty.
To fix this error use the DBCC SHRINKFILE with the EMPTYFILE argument command
dbcc ShrinkFile (mydatabase_Log_2, EmptyFile)
The EMPTYFILE argument moves data from the chosen file to another file in the same filegroup.Once completed , rerun the ALTER DATABASE command