Post reply

Best Practice Advice on Trans Logs, how to automate

  • November 16, 2009 at 8:50 am

    #212667

    All -

    Looking for best practice or way to keep transaction logs cleaned up. Working with (5) test region databases , all set to Simple Recovery. I have saw where Best Practices wants you to stay away from Shrinking the database regularly but wondered if there is some code to run regularly that would keep transaction logs trimmed down to acceptable levels. I thought of doing a Check Point then maybe a ShrinkFile on the log file but looking for opinons on that.

    Example:

    Use myDatabase1;

    checkpoint

    dbcc shrinkfile (myDatabase1_log, 15)

    Any and all help appreciated, thanks !

  • November 16, 2009 at 8:56 am

    #1079727

    Please read through this - Managing Transaction Logs[/url]

    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

  • November 16, 2009 at 9:25 am

    #1079749

    I have read thru the Managing Transact Logs document but was kind of wanting some practical information. Like is the accepted practice to monitor transaction log files regularly and if they get to big, run a checkpoint and shrinkfile on the log file. (simple recovery)

    Thanks

  • November 16, 2009 at 9:31 am

    #1079753

    The accepted practice is to size them for the workload (regardless of recovery model) and leave them alone.

    Shrinking a log is a bad idea unless it's after an unusual operation and you know absolutely 100% that the log does not need to be that size. Repeated shrink/grow leaves you with lots of little VLFs in the log (slows down database recovery) and file-level fragmentation. Neither of which is something you want.

    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

  • November 16, 2009 at 9:35 am

    #1079759

    Thanks I really appreciate the information.

Viewing 5 posts - 1 through 4 (of 4 total)

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