SQL Server 2008 Recovery Models and Transaction log usage

SQL Server 2008 Recovery Models
In SQL Server 2008 there are 3 recovery models you can use: simple, bulk-logged, and full. These recovery models really just define how log management behaves so you can have what you need to perform your desired backups.
As the name implies the focus is really on recovery of databases in case of Disaster occurs.

Simple Mode

If a database is set for Simple Recovery mode than it means that you don’t bother to take log backups. Only Backups available will be Full Backups of Database which you will use in case of Disaster occurs. SQL Server forces the log to truncate when the database writes data to the disk (we call that a checkpoint). SQL Server won’t shrink the log file itself, it will only delete the inactive portion of the log which can re-used.
Note: Even though the Database in Simple Recovery if a huge transaction grows your log to 100GB, even after the transaction completes/Commits portion of the log is marked as inactive and is available for re-use but physically the file remains 100GB until you explicitly want to shrink it.

Full Mode

For a Point-in-Time Recovery to be available this Recovery model is the highest used of all. In this Recovery model we have options for taking backup’s like Full, Transactional log. A Full backup can be used for Restoring databases till the point where backup is taken. If we used Transactional logs with Full Backup then it will let us do a Point-in-Time Recovery. In this Recovery mode everything is fully logged, so you can always recover individual transactions. The log is never truncated by the system after a checkpoint. The Transaction Log is only truncated when you backup the log. As explained above, the log file itself isn’t shrunk, the inactive portion is just deleted and is available for re-use. You can choose to shrink it manually if you like.

Bulk Logged Mode

Bulk Logged Recovery is very similar to a full Recovery mode with one difference which is that Bulk load operations are minimally-logged. The log backup and truncation methods are the same as full recovery mode. This Recovery mode is only used when its needed where you do want logs to be generated for massive Bulk Operations. I always recommend a Backup taken before and after you change to this recovery model from Full Recovery model.

Note: 'Backup log databaseName with truncate_only' is ignored from SQL Server 2008. It won’t fail but doesn’t do anything anymore. The command just gets ignored.
Currently there are only 2 ways to truncate the log while in full mode.
1. Take a log backup. Clearly once the log has been backed up it is no longer needed by the system.
2. Other way is by switching the database to simple mode. Once you switch to Simple Mode, SQL Server thinks you don’t want log backups and truncate the log for you. As explained earlier this process does not shrink the physical file itself.

Leave a Reply

Your email address will not be published. Required fields are marked *