Database Recovery Models

In my previous post, we talked about transaction log shipping and that for transaction log shipping, your database must be set to Full or Bulk-Logged recovery model. Recovery models specify the way SQL Server manages log files. There are three types of recovery models..

• Simple Recovery Model
• Full Recovery Model
• Bulk-Logged Recovery Model

Simple Recovery Model

As the name implies, simple recovery model is just that, simple. SQL Server maintains transaction log only until the database reaches a transaction checkpoint. Each time the database reaches a transaction checkpoint, SQL Server truncates the transaction log. Hence, point in time restores are not allowed if you have to restore the database. You can only restore such a database by using full or differential backups. Which means, you will lose any changes made between the latest full / differential backup and the time you restore the database.

Full Recovery Model

In this mode, SQL Server saves the transaction logs until you back them up or until full database backup occurs. As you can imagine, this model allows for point in time database recovery. You can restore the database first by applying the latest full  / differential backup and then applying any transactions up until the time database was operational. Of course you will have to have a transaction log backup process in place to backup transactions periodically.

Bulk Logged Recovery Model


This model is similar to Full Recovery Model except in the way it handles bulk data operations (For example Bulk Insert). This model uses a process called minimal logging for bulk operations which saves significant process time. This model doesn't allow for point in time database restore operation. It is recommended to use this model only for a short period of time when you are performing bulk operations. Best way to implement this is to switch to bulked logged recovery model before performing bulk operations and then switch back to full recovery model.

Transaction Log File Management

Transaction Log Files, if not handled correctly can become a nightmare when they grow to be too large and suddenly there is no hard drive space and your database is down. I have seen situations where transaction log file has taken so much space on the hard drive that it must be truncated, but it cannot be backed up because there is no space on the hard-drive and no network location where the file can be backed up. Then you are forced to truncate the log file without making a backup, leaving you vulnerable until full database back is performed.

It is absolutely essential that you have a transaction log backup process in place if your database recovery model is Full or Bulk-Logged. If you are not backing up transaction logs in full or bulk-logged recovery model, your log is not being truncated, period. Even when you backup transaction log, remember the log file is only truncated after a restore checkpoint has reached.

If you are not backing up transaction logs and don't need point in time restore in case of a disaster, you should switch your recovery model to Simple. Simple recovery model will automatically truncate the transaction log after the checkpoint.

Also, log file truncation does not shrink the file, it simply clears the file to be reused for future transactions. If your file has grown too large and you want to recover some hard drive space, you must shrink the file manually. You can use the following SQL script

DBCC SHRINKFILE(<logName>, 1)
GO
BACKUP LOG <DBName> TO DISK = N'C:\Backup\TestDb.bak'
GO
DBCC SHRINKFILE(<logName>, 1)
GO


SELECT Name FROM sys.database_files WHERE type_desc='LOG'



NOTE: In SQL 2005 you can also use the following script. Although I will not recommend this because it doesn't backup the log and breaks the transaction log chain which means you can not restore to a point in time because not only you have lost transactions, any future backups of the transactions will be useless until the next full or differential backup.

BACKUP LOG <DBName> WITH TRUNCATE_ONLY

You can also set your database to auto-shrink. But I will recommend against this because it could create performance problems.

Under certain situations transaction logs may not truncate even when you are backing up transaction logs. We will discuss some of those situations in our future blog but usually this should not be a concern for most environments.

That's all there is to it. As always, your comments are welcome!