Msg 5042 - The file cannot be removed because it is not empty and dbcc shrinkfile emptyfile

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]
ALTER DATABASE [mydatabase]  REMOVE FILE [mydatabase_Log_2]


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

 Read More

 SQL Server - Inside the sql transaction logs with fn_dblog and current transaction_id

SQL Server – Read sql transaction Logs with ::fn_dblog

SQL Server – How to remove a sql transaction log file on a database

SQL Server – Transaction Log files sequential

Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer