Post reply

Simple Recovery databases and large log files

  • July 9, 2012 at 3:57 pm

    #258580

    I have searched and googled and I am still confused....I have a 2005 SQL Server with over 100 databases. ALL of the User databases use Simple Recovery. Some of the log files are very large (over 100M and 1 is 35G). All the log files are on their own 50G disk. We are reaching max disk space for that disk.

    I read that the logs are truncated but do not shrink automatically. I have also read that I should not shrink the db's regularly.

    Is there some methodology I should use to do log file maintenance when in Simple recovery? Should I be shrinking the databases that are excessively large on a regular basis to keep disk usage down?

    Any suggestions would be greatly appreciated!

  • July 9, 2012 at 3:59 pm

    #1510626

    Nope. Once-off shrink to get things to a reasonable size, sure. Regular shrink, no.

    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

  • July 9, 2012 at 6:18 pm

    #1510649

    GilaMonster (7/9/2012)

    Nope. Once-off shrink to get things to a reasonable size, sure. Regular shrink, no.

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

    +1

  • July 10, 2012 at 2:33 am

    #1510736

    if the transaction logs grew because the databases were in full recovery without a log backup, I'd say, yes, go ahead and do a shrink on them. Otherewise, they've grown to the size they are to support the transactions run against them. I'd leave them that size because they're very likely to grow right back to that same size again. Why bother shrinking and then paying for the process costs of growing again?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 10, 2012 at 10:09 am

    #1511066

    Thanks for the responses! So from what I am gathering, its better to consider adding more disk space to allow for the incresed size of the log files than to try to keep the log files down to a size that they will most likely not stay at because of normal growth and activity?

  • July 10, 2012 at 10:27 am

    #1511083

    chumphrey 12211 (7/10/2012)

    Thanks for the responses! So from what I am gathering, its better to consider adding more disk space to allow for the incresed size of the log files than to try to keep the log files down to a size that they will most likely not stay at because of normal growth and activity?

    Assuming their size is due to normal growth & activity (probably a safe assumption), yes.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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