How to Remove Secondary Transaction Log File?

Question

I am looking to delete the secondary transaction log file. But when I try to delete the file by ALTER DATABASE REMOVE FILE command it throws me the following errorrnMsg 5042, Level 16, State 1, Line 1rnThe file 'mydatabase_Log_2' cannot be removed because it is not empty. rnHow can I delete the seco

Edwin J. Hoffer · Accepted Answer

Try this n  SELECT o. name AS TableOrIndexn FROM sysfiles fnJOIN dbo. sysfilegroups sn ON f. groupid = s. groupidnJOIN dbo. sysindexes in ON i. groupid = s. groupidnJOIN dbo. sysobjects on ON i. id = object_id(o. name)n AND i. indid in (0, 1)nWHERE f. name = ' My_Logical_File_Name' nUNIONnSELECT i. namen FROM sysindexes i join sysfilegroups f ON i. groupid = f. groupidn JOIN sysfiles l ON f. groupid = l. groupidnWHERE l. name = ' My_Logical_File_Name'  nn

Mariya Beckham · Answer

Thanks Stephen for providing the script, I tried the script, but found no object listed. It didn’t work for me, I am still facing the same problem

David Ross · Answer

Use the DBCC SHRINKFILE with the EMPTYFILE argument commandnn dbcc ShrinkFile (' logical_file_name', EmptyFile) n

Kerry Wilson · Answer

I have tried it but it doesn't work for me. Is there any other way?

Edwin J. Hoffer · Answer

You can also follow the below steps: nn 1.  Connect to the instance of SQL Server Database Engine & expand the instance. n 2.  Expand Databases > right-click the database from which to delete the file, and then click Properties. n 3.  Select the Files page. n 4.  In the Database files grid, select the file to delete and then click Remove. n 5. Click OK. n

Mariya Beckham · Answer

Thanks Andrew, this was really helpful. I had been struggling with this problem for a long time and couldn't figure it out. Your solution fixed it