Return to Home Page

Transaction Log Basics
By Craig S. Mullins

Every SQL Server database has at least two files associated with it: one data file that houses the actual data and one transaction log file. The transaction log is a fundamental component of a database management system. All changes to application data in the database are recorded serially in the transaction log. Using this information the DBMS can track which transaction made which changes to the database.

The Basics of Logging

The CREATE DATABASE statement is used to create a Microsoft SQL Server database. The LOG ON clause is used to identify the transaction log file for the database to be created. Once created, data is stored in the database file, and a record of all modifications to that data is recorded in the transaction log file.

As database modifications are made, the transaction log for the database will grow. Since each database change is logged, you will need to actively monitor the size of the transaction log files because if the data is constantly changing, the log will be continuously growing.

At each checkpoint, Microsoft SQL Server will guarantee that all log records and all modified database pages are written safely to disk. Transaction log files are used by Microsoft SQL Server during database recovery operations to commit completed transactions and rollback uncompleted transactions. Information recorded on the transaction log includes:

• the beginning time of each transaction
• the actual changes made to the data and enough information to undo the modifications made during each transaction (accomplished using before and after images of the data)
• the allocation and deallocation of database pages
• the actual commit or rollback of each transaction

Using this data Microsoft SQL Server can accomplish data integrity operations to ensure consistent data is maintained in the database. The transaction log is used when SQL Server is restarted, when transactions are rolled back, and to restore a database to a prior state. Let’s examine each of these scenarios.

When SQL Server is restarted, each database goes through a recovery process. During restart processing, SQL Server checks to determine which transactions must be rolled forward. This occurs for transactions where it is unknown if all the modifications were actually written from the cache to disk. A checkpoint forces all modified pages to disk. Therefore, it represents the point at which the startup recovery must start to roll transactions forward. Because all pages modified before the checkpoint are guaranteed to be written accurately to disk, there is no need to roll forward anything done before the checkpoint.

When a transaction is rolled back SQL Server copies before images to the database for every modification made since the BEGIN TRANSACTION.

During a recovery scenario you can use the transaction log to restore a database. First, a backup copy of the database must be restored and then subsequent transaction log backups can be restored. This causes a roll forward of the transaction log. During a roll forward SQL Server will copy after images of each modification to the database. Using the logged data SQL Server ensures that each modification is applied in the same order that it originally occurred.

You can see where the transaction log is a useful item to have around in case of database errors, transaction errors, and to ensure data integrity.

But Not All Operations Are Logged

Microsoft SQL Server avoids logging in certain situations to avoid "out of space" conditions caused by rapid growth in transaction log files.

For example, during some large operations, such as CREATE INDEX, Microsoft SQL Server will not log every new page. Instead, SQL Server records enough information to determine that a CREATE INDEX happened, so that it can either be recreated during a roll forward, or removed during a roll back.

Additionally, if the ‘select into/bulkcopy’ database option is set to TRUE, Microsoft SQL Server will not record the following operations in the transaction log: bulk load operations, SELECT INTO statements, and WRITETEXT and UPDATETEXT statements. These four operations usually cause a large volume of data to be changed in the database. As such, logging can slow down these processes, so SQL Server allows you to disable logging for these operations only. However, because these operations are not recorded in the transaction log, SQL Server can not use the restore operation on the transaction log to recover these operations. Since SQL Server has no knowledge of the operations occurring, it can not recover the data.

If you issue one of these operations for a database when the ‘select into/bulkcopy’ option is TRUE, be sure to backup the database so that the changes made by these operations are preserved if a subsequent restore is required.

Transaction Log Backups

To ensure an efficient and effective backup and recovery strategy for your Microsoft SQL Server databases, you will need to implement periodic transaction log backups. A transaction log backup is created using the BACKUP LOG command. A database can be restored to any point in time contained within the sequence of transaction logs you have backed up, up to the point of failure. If you do not backup your transaction logs before truncating them, you will only be able to restore your database to the last database backup you have created.

When Microsoft SQL Server finishes backing up the transaction log, it truncates the inactive portion of the transaction log. This frees up space on the transaction log. SQL Server can reuse this truncated space instead of causing the transaction log to continuously grow and consume more space. The active portion of the transaction log contains transactions that were still running and had not yet completed at the time of the backup.

Microsoft SQL Server will try to take a checkpoint whenever the transaction log becomes 70 percent full, or when a log full error occurs. Additionally, when SQL Server is shut down (unless the NOWAIT option is specified) it will take a checkpoint for each database.

The transaction log should not be backed up if the ‘trunc. log on chkpt’ database option (short for truncate log on checkpoint) is set to TRUE. If you specify truncate log on checkpoint to be true, Microsoft SQL Server will clear out inactive transaction log entries at every checkpoint. This option essentially tells SQL Server that you will not be using the transaction log for restore operations. The transaction log must still be created though, because it is still required to roll back transactions and for SQL Server to determine how to recover databases when it is restarted. Enable checkpoint log truncation only for systems where it is okay for you to lose transactions during the course of a day, because you will only be able to restore your database back to the last database backup that was taken. Applications of this nature are very rare in most production environments.

If a transaction log has been truncated (except by a BACKUP LOG) you should not backup that log until you take a database backup or differential database backup. A differential database backup will backup only data that has changed since the last full database backup.

You should also avoid backing up transaction logs any nonlogged operations have occurred in since the last database backup was created. Instead, create a database backup or a differential database backup if this is the situation.

And finally, if any files are added or deleted from the database, a transaction log backup should not be taken. Instead, you should create a full database backup, after which transaction log backups can be created again.

Changing Database Options

The truncate log on checkpoint option can be changed at the database level. Use the system procedure named sp_dboption to change the configuration settings for a database. For example:

This will change the truncate log on checkpoint option to false for the pubs database. To see a list of all current database options set for a database, simply issue the system procedure without additional parameters, for example:

You can also use Enterprise Manager to set database options. Enterprise Manager is a component of the Microsoft Management Console (MMC) that provides a GUI environment for managing SQL Server databases.

When a database is first created, most of its options will be set to false. In the desktop edition of Microsoft SQL Server, however, the truncate log on checkpoint database option is set to true. This may not be a problem depending on the recovery requirements of the data stored in your desktop SQL Server databases.

You might also consider setting ‘trunc. log on chkpt.’ to TRUE for test databases during your application development cycle. Usually, it is not important to save every test transaction that is attempted while an application is in development.

Conclusion

This has been a brief introduction to using transaction logs with Microsoft SQL Server. The topic of database backup and recovery is complex and we have only touched the surface of how SQL Server implements backup and recovery.

The main lesson to digest from this article is the importance of the transaction log. Too many times new SQL Server databases are implemented with very small transaction logs coupled with using the truncate log on checkpoint option. This is a dangerous combination for mission-critical production applications because it can cause transactions to be lost during a hardware, software, or application failure. And every transaction is precious in most production systems. Make sure your SQL Server databases are protected by planning for and implementing a transaction log as part of your overall SQL Server database backup and recovery plan.

From SQL Server Update (Xephon) June 2000.

© 2000 Craig S. Mullins, All rights reserved.
Home.