SQL Database Recovery Model

Depending on your site database's recovery model, you may encounter issues with excessive log growth. If your database recovery model is set to FULL it is essential to schedule regular backups of your transaction log. This will ensure that at regular intervals the transaction log is emptied of unnecessary items, preventing the log from growing to infinite size and tying up server resources.


First, determine your database's Recovery Model settings. This is most commonly set to FULL or to SIMPLE. To identify this, right click on the database in SQL Management Studio and choose 'Properties' ---> 'Options'.

SQL1.jpg

If your intended database recovery method will be to restore from the most recent database backup (and database backups are being performed regularly) then the recommended recovery method is SIMPLE. SIMPLE will limit log growth as many unneeded entries can be recycled without growing the log. You should still monitor the log file size on a regular basis to ensure it is not growing significantly. Unless you consider it otherwise necessary, this is the recommended recovery model suggested for client databases. 

If your intended recovery method includes restoring the database to a point in time between scheduled database backups you should choose FULL. Databases with Full Recovery Model must have scheduled regular database maintenance which includes transaction log backups. The following MSDN article details how to configure a transaction log backup as part of your regularly scheduled database backups.

Additional resources on the necessity of Transaction Log backups:

SQL Recovery Models Explained
Database Maintenance Best Practices
Huge Log Files

Referenced by:

Have more questions? Submit a request

0 Comments

Article is closed for comments.