How to truncate Mirrored Database Log File

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:ffdbname.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(Logfileid)

or

DBCC SHRINKFILE(Filename,minsize)

 The file will shrink to its maximum extent.

 

 

5 thoughts on “How to truncate Mirrored Database Log File

  1. 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 to disk=‘D:ffdbname.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(Logfileid)

    or

    DBCC SHRINKFILE(Filename,minsize)

    The file will shrink to its maximum extent.
    ———————————————————————————————————-
    IT WORKS IN SQL 2012 TOO WITHOUT REMOVING MIRRORING, THANK YOU…
    ———————————————————————————————————-

Leave a Reply

Your email address will not be published. Required fields are marked *