MS SQL Logging and Recovery

A common question on mind of database administrators is to find out the best way how to back up and recover your data. This document describes some basic concepts about Microsoft SQL Server logging and recovery. MS SQL Server provides three recovery models – simple, full, and bulk-logged.

The simple recovery model is best suited for testing purposes. You shall only use this recovery model when you are willing to loose all your updates between a failure and your last backup. Use this with caution.

Full recovery model is the default recovery model used when you create a database. This recovery model ensures that you safeguard all your data in case of failures. However, this facility comes to a cost. You must ensure proper transaction log backups. Later on, we shall see how this can be managed.

The last recovery model provided by MS SQL Server is known as bulk-logged recovery model. This model is intended strictly as an adjunct to the full recovery model. Microsoft recommends that you use it only during periods in which you are running large-scale bulk operations, and in which you do not require point-in-time recovery of the database.

All actions executed by a database management system are stored in a special log file, called transaction log. This transaction log guarantees Atomicity, Consistency, Isolation, and Durability (ACID) properties over crashes or hardware failures, and even server restarts. In other words, the transaction log records changes made to the database and stores enough information to allow MS SQL Server to recover the database. This recovery process takes place every time a MS SQL Server instance is started, and it can optionally take place every time the MS SQL Server restores a database or a log from backup. The recovery process is that mechanism that reconciles the data and log files. The recovery process has three phases, which centre around the last checkpoint record in the transaction log. These phases are illustrated in Figure 1.1 The phases are called Analysis, Redo, and Undo.

Figure 1 – MS SQL Server (2005 and after) recovery process

MS SQL Server supports multiple physical files for hosting the transaction log. No matter how much physical files there are, the server always treats the log as one contiguous stream of virtual log files (VLFs). Each VLF can be in any of these four states – active, recoverable, reusable, or unused. One can observe the state of each VLF by executing the undocumented command DBCC LOGINFO. A sample output is shown in Figure 2.

Figure 2 – Sample output from DBCC LOGINFO command

The status column in Figure 2 shows the VLF state. A value of 0 means that the file is either in reusable or completely unused states whereas a value of 2 means that the file is either active or recoverable.
The database is considered to be in automatic truncate transaction log mode if any of the below is true:2

  • You have truncated the log using BACKUP LOG WITH NO_LOG or BACKUP LOG WITH TRUNCATE_ONLY;
  • You have set the database to truncate the log on a regular basis by setting the recovery model to SIMPLE;

  • You have never taken a full database backup.

That is, that if you are not performing regular log backups and your database is not in autotruncate mode, your transaction log file will never be truncated. You must manually truncate the log to keep it at a manageable size if you are only doing full database backups.


1. Karen Delaney, Inside Microsoft SQL Server 2005: The Storage Engine, Microsoft Redmond/WA 2007, 152.

2. Ibid., 157.

Article sections: