Archive for the ‘SQL Server 2005 DataFiles Work around’ Category

How to truncate Mirrored Database Log File

June 17, 2008 11 comments

Mirroring is a new feature comes with SQLServer 2005.
Mirroring provides High Availibity of databases without any dataloss.
Mirroring Comes with three flavours
(1) High Performance (asynchronous).
(2) High safety without automatic failover (synchronous).
(3) High safety with automatic failover(synchronous) need withness server.

(Read more in BOL  Look For : mirroring databases [SQL Server])

This document will focus on how to shrink log file while databases are participating in mirroring.

Database files participating in mirroring can’t be shirnked by using truncate_only option with Backup log command.

To shrink Log file of database  participating in mirroring, Backup the log file at any location (can be a local drive or network location)

 BackupLog <DatabaseName> to disk=‘D:\ff\dbname.trn’

Lets check if all transaction is written to disk.

DBCC LOGINFO(‘database name’)

Transaction which are written on disk will have status=0

In case if last transaction show status=2 then backup the database log once again.

Now shrink the file



DBCC SHRINKFILE(Filename,minsize)

 The file will shrink to its maximum extent.