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
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |