Database logs won't shrink

  • 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

  • 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" πŸ˜‰

  • 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?

  • 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" πŸ˜‰

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

    Luckily I'm still young, so I still have a loooong way to go πŸ™‚

  • 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://www.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
  • Thanks for the heads up. I will schedule a job for the rebuild

  • 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