Truncate log for Mirrored Database

  • Hello,

    I am running Mirroring on my Database. Log file for my database growing day by day on Principal server. Recovery Model is Full. Data file is only 2 GB but Transaction Log file is 50 GB.

    I am running 2 Management Plans on Principal Server.

    First Management plan is running Full Database back up every 3rd day and is running fine.

    Second Management Plan is running the following

    a) Transaction log backup.

    b) TSQL Command backup log with no_log

    c) Shrink Database utility.

    transaction log backup works fine but for TSQL command it gives error that log can not truncate for mirrored database.

    Is there any option I can truncate Log file for Mirrored database through Management Plan, without removing mirroring?

    Response would be greatly appreciated.

    Thanks

    Rishabh

  • You cannot truncate the log of a mirrored database. You should not be truncating your log anyway. It breaks the log chain and means that you will not be able to do a point-in-time restore of the db to any point after the log truncation, until you take another full/diff backup.

    a) Transaction log backup.

    b) TSQL Command backup log with no_log

    c) Shrink Database utility.

    Why truncate the log at the 2nd step? You've just backed up the log which will discard all inactive transactions (after backing them up). The only thing that will achieve is to break the log chain.

    Why do you want to shrink your database every 3 days. It results in sever index fragmentation and the DB is just going to grow again as soon as data is added. The repeated shrink/grow can result in fragmentation at a file-system level.

    Have a quick read through these two blog posts please

    http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Another reasons to grow your Tlog is if your Mirroring is not working properly(i.e. latency is more) or if Mirroring is pending on destination DB it won't truncate the log.

    Check and make sure Mirroring is workind smoothly.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • No, you cannot shrink the tlog on a mirrored database without breaking the mirror first.

    Given that your log is 50Gb vs data at 2Gb you're going to need to bite the bullet and turn off mirroring for a moment while you sort this out. Something is clearly amis here and you're going to need to get at things to figure out what which means turning off the mirror. The only job you should need to keep the tlog a sensible size is a regular tlog backup (via maint wizard if necessary). I'd also check this t-log backup actually working (ie you're getting a nice .trn file and it can be restored to a test system) and it is running regularly enough for the number of transactions that are being pumped through the database.

  • Mirroring is working properly.

  • FNS (10/20/2008)


    No, you cannot shrink the tlog on a mirrored database without breaking the mirror first.

    Given that your log is 50Gb vs data at 2Gb you're going to need to bite the bullet and turn off mirroring for a moment while you sort this out. Something is clearly amis here and you're going to need to get at things to figure out what which means turning off the mirror. The only job you should need to keep the tlog a sensible size is a regular tlog backup (via maint wizard if necessary). I'd also check this t-log backup actually working (ie you're getting a nice .trn file and it can be restored to a test system) and it is running regularly enough for the number of transactions that are being pumped through the database.

    I have scedule transaction log back every weekend. nut still no use. Trans log file is growing day by day.

  • rishgup (10/20/2008)


    I have scedule transaction log back every weekend. nut still no use. Trans log file is growing day by day.

    You need to backup the log a lot more than once a week. Depending on activity, once or twice an hour.

    How often are you backing up the log? Is you query the sys.databases view, what does the column log_reuse_wait_desc show for the DB in question?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/20/2008)


    rishgup (10/20/2008)


    I have scedule transaction log back every weekend. nut still no use. Trans log file is growing day by day.

    You need to backup the log a lot more than once a week. Depending on activity, once or twice an hour.

    How often are you backing up the log? Is you query the sys.databases view, what does the column log_reuse_wait_desc show for the DB in question?

    Now I setup Transaction log back up every 2 hours and the log_reuse_wait_desc for database is LOG_BACKUP

  • Then you should be set. That column value indicates that the log space is not been reused because a log backup is required. Once the log backup runs (check and make sure that they are actually running successfully), the space in the log should be reused.

    You can shrink the file down to a reasonable size (say 1GB) and see how things go from there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/21/2008)


    Then you should be set. That column value indicates that the log space is not been reused because a log backup is required. Once the log backup runs (check and make sure that they are actually running successfully), the space in the log should be reused.

    You can shrink the file down to a reasonable size (say 1GB) and see how things go from there.

    Thanks I'll update you guys

  • Thanks to all who give suggesstions. Now my database is all set.

    I set up transaction log backup every 1 hour and its working fine. And it is mirrored as well. Thanks once again to all.

  • Just to add in some details - No need to break mirroring, do anything special on the mirror.

    If you simply use DBCC ShrinkFile on the principle's log file, the resulting change will be applied to the mirror as well in a very small transaction. The mirror size will normally stay perfectly in synch with the principle's size.

    We ran out of disk space on the mirror, so mirroring was suspended and the principle's log grew rather large. After clearing room on the mirror's log drive, resuming mirroring, letting it catch up, and finally letting the next principle log backup take place, we were able to use DBCC ShrinkFile on the principle's log file and the change was instantly reflected on the mirror.

    SQL Server 2005, SP2 and SP3 have worked the same.

    By the way, we don't go around shrinking transaction logs all the time, but this one had grown to 146 Gig, when it is normally about 15 Gig. Yes, it's a fairly large database.


    Student of SQL and Golf, Master of Neither

  • Hi Rishabh ,

    When you backup transaction log a database that is pricipal, transaction log doesn't decrease but precentage usage of transaction log decreases. Execute dbcc sqlperf(logspace) before backup transaction log and see Log Space Used(%) value and re execute after backup . You will see Log Space Used(%) decreased

  • Please note: year old thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It's possible : to shrink transaction file, backup must be performed as there are actives Virtual Log File :

    http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499.aspx

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply