Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
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 '13 at 9:54
    
So, this process doesn't cause any locking or blocking, just a slight performance hit to CPU and I/O? –  Sean Perkins Sep 10 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. –  Phil Hannent Sep 11 at 8:28

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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