Post reply

How to reduce LDF File?

  • February 26, 2011 at 12:29 am

    #231739

    I'm sorry for such easy question, but i'm a begginer DBA and i really need to know something.

    I have several DB's in some DB Server with "Full" Recovery Mode. There is no any Backup runs on that Server at least 3 months. The problem is, that LDF Files were been grown to huge sizes. To reduce LDF Files i've already tried to perform "Shrink", but there is no change - LDF Files stay in same size as before "Shrink". What does work, it to change Recovery Mode to "Simple" and to perform "Shrink". The problem is, that i have approximately 30 DB's on this server and have also different Servers with same problem, so to change each DB to "Simple" Mode and to perform "Shrink" it's not so good for me.

    My question is: How in my case i can reduce LDF Files size without changing DB's Recovery Mode to "Simple" Mode and to "Shrink" after?

    Thanks!

  • February 26, 2011 at 2:00 am

    #1291891

    Switch the database to simple recovery and run shrink. That's what's going to be required, there's no magic 'fix it' switch for this.

    Then decide whether or not you need point-in-time recovery or not. For databases that you do, switch back to full recovery and set up full database backups and log backups. For databases that you don't, leave in simple recovery and just set up full backups.

    Please read through this: http://www.sqlservercentral.com/articles/64582/

    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

  • February 26, 2011 at 3:18 am

    #1291895

    GilaMonster

    Thanks for your answer. If it's only what i need to do, so i will.

Viewing 3 posts - 1 through 2 (of 2 total)

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