SQL Server Performance Forum – Threads Archive
Recovery model of model database
Hello, I just read this advice:quote:Common cause of large transaction log file (.ldf)
Unfortunately the sql server default (except local editions) leaves the databases in full recovery mode.
This menas that if no action is taken no tr log entries will be freed and the log file will eventally fill the disk and crash the system.
The SQL Server installation process is very simple and commonly carried out by inexperienced personel. This will appear to work happily but cause problems later.
I would recommend always setting the model database to simple recovery mode to set the default for new databases.
Source: http://www.nigelrivett.net/TransactionLogFileGrows_1.html
This menas that if no action is taken no tr log entries will be freed and the log file will eventally fill the disk and crash the system.
The SQL Server installation process is very simple and commonly carried out by inexperienced personel. This will appear to work happily but cause problems later.
I would recommend always setting the model database to simple recovery mode to set the default for new databases.
Source: http://www.nigelrivett.net/TransactionLogFileGrows_1.html
However, this site appears to say that this solution is for development servers only:
quote:Now, that solves the problem for this one database, but it will crop again the next time you create a database. A good way to deal with this once and for all is to change the recovery mode of your Model database to Simple or Bulk Logged, since that setting will apply to all future databases you create. Just make sure to update the recovery mode when you database is deployed to a production environment.
This is just a simplistic solution for SQL Server running on a developer machine – see some more in depth information on the MS Support site.
Source: SQL Server recovery model selection (or, what’s this 3GB LDF file?!)
Source: SQL Server recovery model selection (or, what’s this 3GB LDF file?!)
Is it recommended to change the recovery model of the model database to Simple, even on production database servers? Thank you.
jrdevdba
No, I wouldn’t recommend it. The point is, for production servers you should manually set the recovery mode appropriately. Even for production servers there are sometimes reasons to have a database in SIMPLE recovery mode (e.g. reporting databases loaded nightly. etc). For transactional databases in production they should be in FULL recovery mode with regular log backups. You should always deal with this appropriately when managing a production database, irrespective of the recovery mode of the model database.
I wouldn’t suggest to change the recovery model as it would invalidate the recovery model for the new databases that are created, I have no problem in leaving as is. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I would suggest you not to change the Recovery Model to Simple for a production db as you can only recover the db up to the last full backup with the Simple Recovery Model and not to the point of failure.
I don’t change the recovery model of the MODEL database but I change the new database recovery model depends on the company/project requirements…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Thanks for the replies. It sounds like the safest thing to do is leave the model databases in my environment in Full recovery (which they are now), and to set the recovery model on new databases as MohammedU suggested – on a case by case basis. Thanks!
jrdevdba
If you want you can change the MODEL db recovery model to simple where you are sure you don’t need any point in time recovery from the server (Dev) specailly where non DBAs create databases…
But production I will go with case by case basis… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Hi,
Yes, it is always good idea to have full recovery model in product server database, but it depends up to the requirement of particular project or what kind of recovery is expected on the said database. We can change it to simple/bulk-logged but when it comes to critical (all production database) database it hast to be in full recovery mode. Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
Mohammed refers case by case means say for few databases if the point-in-time recovery is not required then you need not to have full recovery model where the job for transaction log backup is not required. But also you need to consider having frequent backups or differential backups to safeguard the data in case of any failure with the server. By all means you should not attempt change system databases recovery model unless it is directed by MS PSS during any support resolution.
quote:Originally posted by jrdevdba
Thanks for the replies. It sounds like the safest thing to do is leave the model databases in my environment in Full recovery (which they are now), and to set the recovery model on new databases as MohammedU suggested – on a case by case basis.
Thanks!
jrdevdba
jrdevdba
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>