Post reply

Database logs won't shrink

  • October 5, 2011 at 6:29 am

    #1390629

    If it’s Synchronous Mirroring it should be instantaneous (i.e. no fall behind)

    The mirror server immediately writes the incoming log to disk, where it is held until it is applied to the mirror database. The log waiting on the mirror's disk is known as the redo queue. The amount of unrestored log waiting in the redo queue is an indicator of the time required to fail over to the mirror database.

    Fore more info:

    http://msdn.microsoft.com/en-us/library/ms187465.aspx

  • October 5, 2011 at 6:34 am

    #1390631

    ruan.keyser (10/5/2011)

    650mb? :pinch:

    The script only returned 2 rows

    so you shrank the log to the smallest available size. Bear in mind this may grow again so you may want to take the advantage to re size it appropriately first if you need to

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 5, 2011 at 6:44 am

    #1390636

    Okay, the database is currently synchronized. I have also set up a scheduled job to back up the logs every hour. This should keep the log size in tact?

  • October 5, 2011 at 6:47 am

    #1390637

    ok, just remember to monitor the log size and periodically check DBCC LOGINFO() to see the status of the VLFs

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 5, 2011 at 6:50 am

    #1390638

    Will do, thanks a lot for the help and wisdom.

    Luckily I'm still young, so I still have a loooong way to go 🙂

  • October 5, 2011 at 7:35 am

    #1390669

    Since you shrunk the database, you now need to rebuild all your indexes to fix the fragmentation.

    As for the log, these may be useful.

    Managing Transaction Logs[/url]

    http://qa.sqlservercentral.com/articles/Transaction+Log/72488/

    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

  • October 5, 2011 at 7:40 am

    #1390676

    Thanks for the heads up. I will schedule a job for the rebuild

  • October 5, 2011 at 7:45 am

    #1390685

    ruan.keyser (10/5/2011)

    Thanks for the heads up. I will schedule a job for the rebuild

    You might as well start with the best way to do it...

    http://sqlfool.com/2011/06/index-defrag-script-v4-1

Viewing 8 posts - 16 through 22 (of 22 total)

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