Post reply

changing recovery model on production DB

  • June 7, 2012 at 6:15 am

    #143498

    We want to change recovery model of a production database from Full to Simple as we will have no need to recover the daily transactions (it gets backed up mightly).

    Any cautions in doing so while it is in use?

  • June 7, 2012 at 6:36 am

    #1498106

    No considerations in changing it in use.

    So the business is quite happy with potentially losing up to 24 hours of data in the case of a disaster?

    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

  • June 7, 2012 at 6:45 am

    #1498111

    Yes - the data is pulled from another database.

    Thank you for your quick reply!

    Will you meet me at the airport? 🙂

  • June 7, 2012 at 9:57 am

    #1498275

    At what point will my transaction logs go away?

  • June 7, 2012 at 10:07 am

    #1498283

    What do you mean by 'go away'

    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

  • June 7, 2012 at 10:26 am

    #1498295

    I was thinking (mistakenly, no doubt) that the transaction log would not be needed when switching to simple recovery model.

    So, let me pose the question: Given a transaction log that is very large and after switching from full to simple recovery, what action, if any, should we take to shrink it?

    Also, how is a transaction log used when a database has a simple recovery model? Why does switching to a simple recovery model keep it from becoming obese?

  • June 7, 2012 at 10:31 am

    #1498299

    I am reviewing this: http://www.sqlservercentral.com/articles/Administration/64582/[/url].

    I am concluding that I need to shrink the transaction log as a one-time operation.

    Thanks...

  • June 7, 2012 at 11:06 am

    #1498319

    inevercheckthis2002 (6/7/2012)

    I was thinking (mistakenly, no doubt) that the transaction log would not be needed when switching to simple recovery model.

    Incorrect. All data modifications are logged, always

    So, let me pose the question: Given a transaction log that is very large and after switching from full to simple recovery, what action, if any, should we take to shrink it?

    None initially. Monitor for a while and see how much of the log gets used at max (probably by index rebuilds or huge transactions), then consider a once off shrink to that size + 20% or so.

    Also, how is a transaction log used when a database has a simple recovery model?

    Same way it's used when a database is in full recovery.

    http://www.sqlservercentral.com/articles/Administration/75461/

    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

Viewing 8 posts - 1 through 7 (of 7 total)

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