Truncating logs of mirrored database

Posted by Ashish Kaushal @ http://dbgeek.wordpress.com/2008/08/07/truncating-logs-of-mirrored-database/

Log files of mirrored database grow fast because there is no way to truncate logs of a mirrored database. The only way to truncate the logs is to remove the mirroring (not pause). Truncate the logs then reconfigure the mirror back.

Remove mirror

Right click on database > select Mirror... ("..." three dots means that clicking here will open a window)

Click on "Remove Mirroring" button.

Truncate Logs

Once mirroring is removed. Follow these steps to truncate logs:

1. By backing up the logs we make sure that active logs entries are flushed out on disk)

Backup log [dbname] to disk = 'd:\db.trn'

2. Truncate all the inactive enteries from the log file. because we have flushed the logs in step 1. This step will almost truncate the log file back to its initial size.

Backup log [dbname] with TRUNCATE_ONLY

3. Check the logical name of log file.  Its the "name" field of log file entry usually have fileid "2″

Exec sp_helpdb [dbname]

4. After the log file is truncated it does not release the free space to OS. You will need to shrink the log file in order to release that space back to OS. You must go to that database by using USE, in order to shrink log file.

Use [dbname]

DBCC Shrinkfile ([logical_name_of_log_file], TRUNCATEONLY)

5. Check if the log file size.

DBCC SQLPerf(Logspace)

6. Reconfigure the mirror.