Home > SQL Server 2005 DataFiles Work around > How to truncate Mirrored Database Log File

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

or

DBCC SHRINKFILE(Filename,minsize)

 The file will shrink to its maximum extent.

 

 

  1. Ann
    March 10, 2009 at 9:44 pm

    Do I have to remove mirroring before shrinking the log
    files ?

  2. msufian
    March 10, 2009 at 10:30 pm

    You don’t have to remove DB mirroring.

  3. December 12, 2009 at 1:13 am

    Awesome… really great matter. I am goin to write about it as well!

  4. February 27, 2011 at 4:19 am

    Here is script to truncate all your database. We run it using SQL Server Agent job. Works like a charm. We use it to truncate transaction logs when database running with SQL mirroring.

    DECLARE @sql VARCHAR(MAX)
    SET @sql = ”
    SELECT @sql = @sql+

    USE [‘ + name + ‘]
    BACKUP LOG [‘ + name + ‘] TO DISK = ”\\CLOUD\Root\Databases\’ + name + ‘_’ + convert(varchar(8),getdate(),112) + ‘_log.bak”
    DBCC SHRINKFILE (”’ + name + ‘_log”, 1, TRUNCATEONLY)’
    FROM sys.databases
    WHERE name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘pubs’, ‘Northwind’, ‘ReportServer$SQL2005’, ‘ReportServer$SQL2005TempDB’)
    AND user_access_desc = ‘MULTI_USER’
    AND is_read_only = 0
    AND state_desc = ‘ONLINE’

    –PRINT @sql
    EXEC(@sql)

  5. February 27, 2011 at 4:20 am

    Here is SQL script we use to backup all our database. We use it with SQL job:

    DECLARE @sql VARCHAR(MAX)
    SET @sql = ”
    SELECT @sql = @sql+

    BACKUP DATABASE [‘ + name + ‘] TO DISK = ”\\CLOUD\Root\Databases\’ + name + ‘_’ + convert(varchar(8),getdate(),112) + ‘.bak”’
    FROM sys.databases
    WHERE name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘pubs’, ‘Northwind’, ‘ReportServer$SQL2005’, ‘ReportServer$SQL2005TempDB’)
    AND user_access_desc = ‘MULTI_USER’
    AND is_read_only = 0
    AND state_desc = ‘ONLINE’

    –PRINT @sql
    EXEC(@sql)

  6. February 27, 2011 at 4:25 am

    Here is little VBS script we use to delete old backups of databases and transaction log backups. We use SQL job (cmdexec type command) to run daily
    cscript /nologo \\CLOUD\Root\Databases\DeleteOldDatabases.vbs

    Here is VBS script to run:
    ‘Start
    Option Explicit
    on error resume next
    Dim oFSO
    Dim oFolder
    Dim oFileCollection
    Dim oFile
    Dim iDaysOld
    Dim strExtension

    ‘Definitions
    iDaysOld = 7
    strExtension = “.bak”
    Set oFSO = CreateObject(“Scripting.FileSystemObject”)
    set oFolder = oFSO.GetFolder(“\\CLOUD\Root\Databases\”)
    set oFileCollection = oFolder.Files

    ‘Walk through each file in this folder collection.
    For each oFile in oFileCollection
    ‘msgbox (Now()- iDaysOld),vbOKOnly
    If oFile.DateLastModified < (Now() – iDaysOld) Then
    If (strExtension = "") Or (Right(UCase(oFile.Name), Len(strExtension)) = UCase(strExtension)) Then
    oFile.Delete(True)
    End If
    End If
    Next

    'Clean up
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFileCollection = Nothing
    Set oFile = Nothing
    'End

    All 3 codes work together. At 2AM we run transaction backup, at 3AM we backup databases and at 4 AM we delete old backup files that is older than 7 day. You can adjust your time frame using VBS script. Everything is working on production using SQL mirroring.

  7. February 27, 2011 at 4:29 am

    I hope this 3 scripts can save some people time and energy. I personally spent a lot of time finding right answers and most of people don’t know what they are talking. I never able to find WORKING examples. So here you go, enjoy. If you find them useful please let me know or leave backlink to our company site.

    • April 11, 2011 at 5:43 am

      Good Info.

      Thanks..!!

    • Gary Magadzyre
      June 5, 2013 at 7:58 am

      So do you those 3 scripts as steps in that order?

  8. Nadir Qudrat
    December 21, 2014 at 10:50 pm

    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:\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(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…
    ———————————————————————————————————-

  1. October 14, 2014 at 3:49 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: