impact of changing recovery model
Question
Friday, December 31, 2010 1:56 PM
i am using SQL server 2005 enterprise edition i want to know the impact of changing recovery model simple to full on production database.
there any threat to change the recovery model please tell me.
Thanks
All replies (5)
Friday, December 31, 2010 2:36 PM ✅Answered
Simple recovery model allows restore only up to the last full backup, while in full recovery model you can get point in time recovery.
>>there any threat to change the recovery model please tell me.
You can change recovery model from simple to full (it does not require restart of sql server service etc.) In terms of **threat** there is none and full recovery model is desirable in any production enviornment.
however, you would have to take a full backup immediately after changing recovery model from simple to full. Also, as suggested you need to perform periodic transactiong log backup based on your workload.
The process is documented in SQL Server Books online
Considerations for Switching from the Simple Recovery Model
http://msdn.microsoft.com/en-us/library/ms178052.aspx
Friday, December 31, 2010 3:11 PM ✅Answered
Hi Pawan
As Jason has pointed out, when changing the recovery model from simple to full, you need to watch out for the database transaction log. Unlike simple recovery model, full recovery model will keep the transaction log until the transaction log gets backed up. Thus, you need to plan and monitor it. The biggest threat might be the disk space, and how you set up the transaction log.
If you have a really active production database, the transaction log can grow really quickly. You need to see if you have enough disk space to accomodate the transaction log growth. If you run out of disk space, the database will stop processing more transaction.
On the transaction log setting, you can also specify the initial size, max size and growth amount. You will need to plan for this, if you set up a really small max size for the transaction log, and that size is reached as the transaction log grows, it also can cause the database to stop processing more transaction. You also don't want to set up a good sized initial size and growth amount, since they will avoid the transaction log to acquire more space from the OS frequently (which can cause some delay in processing transaction).
When you change the recovery model from simple to full, one thing that you will need to do is to backup the transaction log of that database regularly. This should help to keep the transaction log size in check.
I must say, with good planning and monitoring, you can mitigate the threats pretty easily.
Thank you
Lucas
Friday, December 31, 2010 2:15 PM
Hi
At the moment your transaction log isn't filling up.
If you set your production to Full Recovery mode you need to manage your transaction log through backups, else your trans log will fill up, stopping you from doing any more transactions.
Regards,
Jason
MCITP BI Developer 2008 - MCTS SQL Server 2005
Sunday, January 2, 2011 8:47 AM
Hi
With SIMPLE recovery model LOG file size is contoled by SQL Server (70% FULL CheckPoint is running) , however with FULL recoveru model there is no control, I mean internally LOG file is divided into Virtual Log file that can be re-used only if you BACKUP LOG file otherwise it will be growing (more Virtual Log files will be created) and if you do not pay attentiopn you are about to foind running out of disk space....
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Wednesday, October 11, 2017 7:13 AM
Hi Pawan Rajput
I don't believe there is any risk to the transactions occurring while changing the recovery model.
Simple recovery model means that the transaction is written to log, applied to the database and as soon as this is done, the transaction entry is overwritten by a new transaction - while the full recovery model requires a backup prior to allowing the transaction log entry to be overwritten... thus the file growth consequence.
Your ATOMIC transactions are not at risk of being incomplete but the management of the log entries changes.