6

I need to switch to FULL recovery model for several SQL Server 2008 databases. However these are in production. Whilst doing these things out of hours is good practice I wanted to plan for any down time or interruption in service, no matter how brief.

Does anybody know the impact changing the recovery model to Full has on a production database?

Clarification: I have a whole process scripted (backup's and logging) and tested for log shipping and restoring the other end. However the one thing I couldn't find details on was if changing the model would have an impact on availability or performance during the process.

3
  • 3
    There won't be any disruption but please also remember to take a initial full backup (otherwise it remains in pseudo-simple mode).
    – mission
    Aug 13, 2013 at 9:54
  • So, this process doesn't cause any locking or blocking, just a slight performance hit to CPU and I/O?
    – AKDiscer
    Sep 10, 2014 at 16:39
  • @SeanPerkins There was nothing noticeable during my switch over. The server was not under load (due to it being late at night) but the process only took a few seconds and then it was done. We don't have long running locks, but by the looks of it its just not clearing the log immediately which probably has the effect of delaying the I/O for when you do truncate. Sep 11, 2014 at 8:28

1 Answer 1

6

I've never had any issues from switching from simple to full. You will have to consider your backups tho.

Microsoft has some information about making the switch in the link below,

http://technet.microsoft.com/en-us/library/ms178052(v=sql.105).aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.