Post reply

Best Practice to maintain the grwing Databases

  • September 30, 2009 at 10:36 am

    #385471

    Hello,

    I used to Backup the Database in SQL 2000 and then truncate the Log File and later shrink the log File to free up the space,

    Now we have upgraded to SQL 2008,

    I am not sure What is the practice now?

    Any Help is much appreciated,

    Thanks,

    Dave

  • September 30, 2009 at 1:57 pm

    #1060168

    What you were doing in SQL Server 2000 was incorrect - you should never schedule a truncate of the log (breaks the log chain), or schedule a shrinkfile or shrinkdatabase (causes index fragmentation on the data file as well as file fragmentation on both mdf/ldf files).

    What you should do is decide whether or not you need full recovery model. If you have the requirement to restore to a point in time in the case of a disaster, then you need to be in full recovery model. If you are in full recovery model, you need a backup plan that includes a full backup (usually daily, could be weekly depending upon requirements) and *frequent transaction log backups* (usually every hour or more often, again depending upon requirements).

    Please review the article in my signature about Managing Transaction Logs. Gail outlines the process very well.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • September 30, 2009 at 2:09 pm

    #1060176

    dsohal (9/30/2009)

    I used to Backup the Database in SQL 2000 and then truncate the Log File and later shrink the log File to free up the space

    :crying: shhhh... tell nobody, especially your boss.

    dsohal (9/30/2009)

    Now we have upgraded to SQL 2008,

    I am not sure What is the practice now?

    Best practice didn't change but, you were not following best practice.

    Determine the backup/recovery strategy that better fits your Business then implement. Hint: Simple or Full.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • October 1, 2009 at 6:39 am

    #1060391

    Thanks a Lot for your advice, I wil follow that, but I do nort see a link with your signature

  • October 1, 2009 at 6:41 am

    #1060393

    Sorry I got it Thanks Again

  • October 1, 2009 at 6:41 am

    #1060394

    Gails article on transaction logs

    http://www.sqlservercentral.com/articles/64582/

    Glad you got it 😛

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

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

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