The Transaction Log

In the past two Certifiably SQL columns, I discussed the automation of routine tasks in SQL Server, using a backup task as an example. (For more information, see "Alerts and Operators," May 2000, and "Defining Jobs," June 2000.) Next month, I'll dig deeper into backup and restore processes. But before you can plan an effective backup and restore strategy, you need to understand the interaction among SQL Server's transaction log, checkpoints, and automatic recovery process.

Microsoft's exam topic guides don't specifically reference the transaction log. Nevertheless, many questions on both the administration and the implementation exams assume that you're familiar with how the transaction log works and the checkpoint and recovery processes. Having a firm grasp of these topics is important for both database developers and administrators.

The transaction log is the part of the database to which SQL Server writes all transactions before writing them to the database. A transaction can be either one change to the database or a series of related changes that SQL Server must complete together or not at all. A classic example of a transaction is when a bank withdraws $100 from your checking account and deposits the money into your savings account. For SQL Server to complete the transaction—and for the bank to balance its books, both steps must occur. Except for some nonlogged operations, such as a fast bulk copy of data into a table, the log records every transaction, letting you recreate the sequence of changes to the database in case of a system crash or other problem.

The transaction log keeps your data consistent by guaranteeing that SQL Server either applies all the changes that are part of the transaction or none of the changes. If the system goes down unexpectedly or you have to bring the machine down in the middle of one or more transactions, whenever the SQL Server service restarts, SQL Server performs "automatic recovery," searching through each database’s transaction log and determining which transactions to complete, or "roll forward," and which to cancel, or "roll back."

If your system or disk crashes, the transaction log lets you recover the database to its current state. You recover the database by restoring your most recent full backup, then applying all changes recorded in the log since that backup. SQL Server's log-shipping functionality lets you maintain a backup or standby server and apply each log—and, thus, all changes—to that server as soon as the backup is completed on the primary server. However, with such tools as Data Transformation Services (DTS) and replication to move data from one server to another, log shipping is less common in SQL Server 7.0. Let’s take a close look at the transaction log, checkpoints (which cause SQL Server to write changed pages in memory to the database on disk), and SQL Server's automatic recovery process.

An Overview

In SQL Server 6.5, the transaction log is actually a database table called syslogs. Administrators usually configure the log on a separate device from the one that contains data. SQL Server 7.0 stores the log in a separate file, whose default extension is .ldf. Regardless of which version of SQL Server you use, you should store the log on a separate physical disk so that if you lose the disk containing the data, you’ll still have access to the most recent database backup and the transaction log. SQL Server doesn’t place the log on a separate disk by default, so when you're creating your database, you need to take this step to safeguard your database.

By default, SQL Server sets aside 25 percent of the database for the transaction log. However, a very active database may need a larger log, and 25 percent of the database might be too large a log for a very large database. If you mainly use a database for decision support with minimal updating, your log could be less than 10 percent of the database. When deciding how much space to set aside for the transaction log, consider how frequently you’ll back up the database and thus free up space in the log. Monitoring the log size is more important in SQL Server 6.5 than in SQL Server 7.0, in which the log can automatically grow as needed. In SQL Server 6.5, if the log grows beyond the space you've allowed, nobody can access the database until you truncate the log. To keep the log from growing so large that it fills up the disk, you need to set the option for the maximum permitted file size for the log.

In addition to storing the log on a separate disk from your data, you should also avoid storing the log on a RAID 5 array. Because SQL Server writes the log sequentially, entry after entry, and reads it the same way during backup or recovery, the RAID 5 array’s multiple heads provide no performance benefits. With a disk dedicated to the log, the disk heads can move steadily through the sectors. But when the log and data share a RAID 5 array, the disk heads will likely have to skip all over the disk to find the requested record, slowing down the log-writing process. In addition, you find no benefit to calculating parity information for the log, which you’ll read once or twice, then discard. You can, however, place the transaction log on a RAID 0 disk array, which mirrors the log and gives you extra redundancy in case of a disk failure.

How the Log Works

When SQL Server processes a transaction, it changes the data in cache—an area of memory where SQL Server stores recently used data pages and pages it’s currently modifying. If the required pages aren’t already in cache, SQL Server reads them in and makes the changes. SQL Server then flags the changed pages in cache, also known as "dirty pages." At this point, pages in cache and corresponding original pages in the database are different. When the transaction reaches the commit point, which is when all the transaction steps are complete, SQL Server writes the changes to the transaction log on the disk. After the database management system commits the transaction, SQL Server will write the changes to the database—even if the system fails—because the log contains the entire sequence of changes that the transaction made. If the system fails, when you restart SQL Server, the database system will recover the changes and write them to the database. When the next user queries the database, he or she will see the updated records.

If you need to process several SQL statements together as a transaction, you can preface the first SQL command with a BEGIN TRANSACTION statement, list the SQL commands, then close the transaction with a COMMIT statement. In SQL Server, a single SQL statement, such as an UPDATE, begins with an implied BEGIN TRAN statement and ends with an implied COMMIT statement. So, after the operation is complete in memory, SQL Server writes it to the log. However, not all relational database management systems (RDBMSs) operate this way. For example, with Oracle, you have to remember to issue a COMMIT statement, or the RDBMS will discard your changes.

After the operation is complete in memory, SQL Server writes the changes to the transaction log. The transaction log records changes to the database, data from the row, and relevant data about each operation performed. For an inserted record, the log shows the entire inserted row. For a delete operation, it contains the deleted record. And for an updated record, the log contains the old and new versions of the row.

Checkpoints

After SQL Server changes the data in memory and records the changes in the transaction log, the checkpoint process writes the changes to the database. One way to describe a checkpoint is as a synchronization between memory and disk. A checkpoint writes all changed pages to the disk. It doesn't write only committed transactions, although this misstatement appears frequently, even in some Microsoft materials. (See the sidebar "Setting the Record Straight" for a clarification of BOL's documentation about SQL Server 7.0 checkpoints.) Therefore, the checkpoint might write some transaction changes to the database on disk before the transaction is committed. But because SQL Server wrote both the log and the data pages to disk, it can roll back the changes if the transaction doesn’t complete.

The checkpoint process wakes up every minute and determines how many changes SQL Server must write to the database on disk. If the checkpoint process finds enough changes, it issues a checkpoint instruction that writes all changed pages to disk. If the process doesn’t find a sufficient number of changes, it doesn't issue a checkpoint. You can configure the recovery interval to control how frequently the checkpoint process writes changes to the database. The recovery interval is a system-wide setting that controls how many minutes the recovery process takes per database. The SQL Server 7.0 default is about 1 minute per database; the SQL Server 6.5 default is 5 minutes. To specify a longer recovery interval, right-click the server name in Enterprise Manager, open the SQL Server Properties dialog box, and select the Database Settings tab, as Figure 1 shows. You can then set the Recovery interval. (SQL Server Books Online—BOL—implies that the Recovery interval number is a multiplier, so that if you type 10 in the recovery interval box, the recovery process will take 10 times longer than normal. Although the process will take 10 times longer, this is true only because "normal" is about one minute.) The default of zero means "fast recovery." Like many defaults in SQL Server 7.0, a zero means that the system automatically determines the optimum value.

If you set a shorter recovery interval than the default, the checkpoint process writes changes to the database more frequently, so it has fewer changes to write each time. If, however, you specify a longer interval, SQL Server lets more changed pages build up in memory before writing them to disk. Most end users don’t notice checkpoints, although a long recovery interval could produce fewer checkpoints with more noticeable slowdown of operations.

Reducing Workload at a Checkpoint

In Windows NT, the individual worker threads scan the buffer cache (the space available for caching data), write out dirty pages, and free up pages for incoming data. The threads do these housekeeping chores in the small interval between when they schedule an asynchronous read and when the read completes. The result is that when a checkpoint occurs, the checkpoint instruction has less work to do and can perform it faster. Worker threads write out both log and data records asynchronously, reducing the impact of a commit or a checkpoint.

The lazywriter is a thread that regularly examines the buffer cache, looking at the size of the free buffer list. You need free buffers to be able to read data into cache as needed or in anticipation of need. If the amount of free buffer space falls below a certain threshold (depending on the size of your cache), the lazywriter writes dirty pages to disk and reclaims unused pages, freeing up space for incoming data. When SQL Server runs on Windows 98 or Windows 95, neither of which perform asynchronous writes, the lazywriter does most of the work of freeing up cache space and writing dirty pages.

Note the difference in functionality between worker threads and the lazywriter versus checkpoints: The worker threads and lazywriter make sure that free space is available in the cache, whereas the checkpoint ensures that SQL Server writes changes to disk.

Automatic Recovery

As I said earlier, whenever SQL Server starts, the automatic recovery process searches the transaction log and identifies which transactions are committed and can be rolled forward and which are incomplete and must be rolled back. SQL Server can roll forward all committed transactions because the transaction log has all the necessary information about that transaction. For transactions that SQL Server hasn’t committed, two possibilities exist. If SQL Server hadn’t written anything to disk and all changed pages were still in memory when the system failed, all the changes would vanish from memory as if transaction never happened. In this case, instead of modifying the database, you must rerun the transaction. If, however, the checkpoint wrote part of the transaction to disk before the system went down, you must undo the changes. Returning to the earlier bank transaction example, if the system failed after the withdrawal but before the deposit, when the system revived, it must void the withdrawal and credit the money to your checking account. In the same way, you must roll back an incomplete transaction and rerun it by using the information in the transaction log to give SQL Server the exact changes. When you write the application code for your transaction-processing program, be sure to allow for rollbacks and resubmitted transactions.

An alternative is to perform a manual restore, which lets you control the recovery process (I'll cover the manual restore process in detail in an upcoming article about backup and restore strategies). When you restore a database, you may have to restore a full database backup and several transaction log backups. Suppose that you back up your database on Sunday and the transaction log every night. During each backup, SQL Server truncates the log, removing older transactions applied to the database to free up space for the next set of changes. This means that when you back up the log on Tuesday evening, you’re backing up only the changes made since Monday’s log backup, not all the changes since Sunday. If SQL Server didn’t truncate the log, by Saturday, you’d have a large backup to manage or you might even overflow the space allocated for the log. If your system failed on Thursday, you’d have to restore the full database backup from Sunday, then restore each day’s transaction log in the correct sequence.

When performing the manual recovery and restoring the first transaction log, don’t let SQL Server perform the recovery process. Suppose that this first log has part of your transaction (the withdrawal of $100 from checking). Automatic recovery would see this as an incomplete transaction and roll it back. Then when you restored the second log and the rest of the transaction (the deposit of $100 into savings), you’d have inconsistent data. To guarantee data consistency, restore all logs except the last one by using the NO RECOVERY option (more about this option in the upcoming article on backup and restore processes). Next, you can restore the final log, then perform the recovery. Now SQL Server has all the information it needs about any transaction and can correctly decide whether to roll forward or roll back. If you're curious about what the automatic recovery process does, you can look at the SQL Server Logs under Management in Enterprise Manager. You'll see the recovery process entries in the first few dozen messages after startup.

Putting It All Together

The graph in Figure 2 illustrates five transactions in SQL Server 7.0 in relationship to a checkpoint and an unexpected system shutdown. Let’s examine each transaction and see what happens at the recovery stage.

Transaction 1. SQL Server completed and committed this transaction before the checkpoint and before the system failure. When SQL Server recovers after the failure, no action is needed because SQL Server has already written the transaction to both the log and the database.

Transaction 2. SQL Server had committed this transaction and partially written it to the database at the checkpoint, so the rest of the transaction is in the log on disk. SQL Server can roll this transaction forward and complete writing it to the database.

Transaction 3. This transaction started after the last checkpoint and was also committed before the system failure, so the entire transaction was written to the log. The recovery process can therefore write it to the database in a roll forward.

Transaction 4. This transaction started before the checkpoint, so some parts were written to the database. But at the time of the system failure, this transaction had not been committed, so the recovery process must roll it back. The rollback includes undoing the changes written at the checkpoint and reverting the data to its state before the changes were made. The before version of the data is in the log, so it can be restored to the database.

Transaction 5. The last transaction started after the checkpoint and never reached the commit point, so SQL Server didn’t write anything to the log or to the database. Existing only in memory, this transaction vanished when the system failed and must be recreated. If another process (perhaps the lazywriter) did write this to the disk, the writes must be rolled back.

Understanding the transaction log, checkpoints, and the recovery process will help you administer and design your database. You can also use this knowledge to design and implement an effective backup strategy. To test your knowledge about the transaction log, checkpoints, and SQL Server's automatic recovery process, see the sidebar "Practice Questions—The Transaction Log." You can check your answers by reading the sidebar "Answers to Practice Questions—The Transaction Log."