Every SQL Server database has its own transaction log that keeps a record of all data modifications in a database (insert, update, delete) in the order in which they occur. This information is stored in one or more log files associated with the database. The information stored in these log files cannot be modified or viewed effectively by any user process.
SQL Server uses a write-ahead log. The buffer manager guarantees that changes are written to the transaction log before the changes are written to the database. The buffer manager also ensures that the log pages are written out in sequence so that transactions can be recovered properly in the event of a system crash.
The following is an overview of the sequence of events that occurs when a transaction modifies data:
Send a COMMIT acknowledgement to the client process.
The end of a typical transaction is indicated by a COMMIT record in the transaction log. The presence of the COMMIT record indicates that the transaction must be reflected in the data-base or be redone if necessary. A transaction that is aborted during processing by an explicit rollback or a system error will automatically undo the changes made by the transaction.
Notice that the data records are not written to disk when a COMMIT occurs. This is done to minimize disk I/O. All log writes are done synchronously to ensure that the log records are physically written to disk and written in the proper sequence. Because all modifications to the data can be recovered from the transaction log, it is not critical that data changes be written to disk right away. Even in the event of a system crash or power failure, the data can be recovered from the log if it hasn't been written to the database.
SQL Server ensures that the log records are written before the affected data pages by recording the Log Sequence Number (LSN) for the log record making the change on the modified data page(s). Modified, or "dirty," data pages can only be written to disk when the LSN recorded on the data page is less than the LSN of the last log page written to the transaction log.
When and how are the data changes written to disk? Obviously, they must be written out at some time, or it would take forever for SQL Server to start up if it had to redo all transactions in the log. Also, how does SQL Server know during recovery which transactions to reapply , or "roll forward," and which transactions to undo, or "roll back." The following section looks at the mechanisms involved in the recovery process.
The Checkpoint Process
During recovery, SQL Server examines the transaction log for each database and verifies whether the changes reflected in the log are also reflected in the database. In addition, it examines the log to determine if any data changes were written to the data that were caused by a transaction that didn't complete before the system failure.
As discussed earlier, a COMMIT writes the log records for a transaction to the transaction log (see Figure 31.1). Dirty data pages are written out either by the Lazy Writer process or the checkpoint process. The Lazy Writer process runs periodically to check whether the number of free buffers has fallen below a certain threshold, reclaims any unused pages, and writes out any dirty pages that haven't been referenced recently. (For more information on the Lazy Writer process and SQL Servers I/O architecture, see Chapter 33, "SQL Server Internals.")
Figure 31.1. A commit writes all "dirty" log pages from cache to disk.
The checkpoint process also scans the buffer cache periodically and writes all dirty log pages and dirty data pages to disk (see Figure 31.2). The purpose of the checkpoint is to sync up on disk the data with the changes recorded in the transaction log. Typically, the checkpoint process finds little work to do because most dirty pages have been written out previously by the worker threads or lazywriter process.
Figure 31.2. A checkpoint writes log pages from cache to disk, and then writes all "dirty" data pages.
The following list outlines the steps that SQL Server performs during a checkpoint:
Figure 31.3 shows a simplified version of the contents of a transaction log after a checkpoint. (For simplicity, the checkpoint records are reflected as a single log entry.)
Figure 31.3. A simplified view of the end of the transaction log with various completed and active transactions, as well as the last checkpoint.
The primary purpose of a checkpoint is to reduce the amount of work the server needs to do at recovery time to redo or undo database changes. A checkpoint can occur under the following circumstances:
The frequency of automatic checkpoints is determined by the setting of the recovery interval for SQL Server. However, the decision to perform a checkpoint is based on the number of records in the log, not a period of time. The time interval between the occurrence of automatic checkpoints can be highly variable. If few modifications are made to the database, the time interval between automatic checkpoints could be quite long. Conversely, automatic checkpoints can occur quite frequently if the update activity on a database is high.
The recovery interval does not state how often an automatic checkpoint should occur. It is actually an estimate of the amount of time it would take SQL Server to recover the database by applying the number of transactions recorded since the last checkpoint. By default, the recovery interval is set to , which means SQL Server will determine the appropriate recovery interval for each database. It is recommended that you keep this setting unless you notice that checkpoints are occurring too frequently and are impairing performance. Try increasing the value in small increments until you find one that works well. Just be aware: The higher you set the recovery interval, the fewer checkpoints will occur, but the database will likely take longer to recover following a system crash.
If the database is using either the full or bulk-logged recovery model, an automatic checkpoint occurs whenever the number of log records reaches the number that SQL Server estimates it can process within the time specified by the recovery interval option.
If the database is using the simple recovery model or if the truncate log on checkpoint option is enabled, an automatic checkpoint occurs whenever the log becomes 70 percent full or the number of log records reaches the number that SQL Server estimates it can process within the time specified by the recovery interval option. If using the simple recovery model, the automatic checkpoint also truncates the unused portion of the transaction log prior to the oldest active transaction.
The Recovery Process
When SQL Server is started, it verifies that completed transactions recorded in the log are reflected in the data, and that incomplete transactions whose changes are reflected in the data are rolled back out of the database. This is the recovery process. Recovery is an automatic process performed on each database during SQL Server startup. Recovery must be completed before the database is made available for use.
The recovery process guarantees that all completed transactions recorded in the transaction log are reflected in the data, and all incomplete transactions reflected in the data are rolled back. During recovery, SQL Server looks for the last checkpoint record in the log. Only the changes that occurred or were still open since the last checkpoint need to be examined to determine the need to be redone (rolled forward) or undone (rolled back). After all the changes are rolled forward or rolled back as necessary, the database is checkpointed and recovery is complete.
The recovery algorithm has three phases that are centered around the last checkpoint record in the transaction log, as shown in Figure 31.4.
Figure 31.4. The phases of the recovery process.
A description of these phases is as follows :
Now examine the transactions in the log in Figure 31.4 and determine how they will be handled during the recovery process:
In a nutshell , this type of analysis is pretty much the same analysis the recovery process would do. To identify the number of transactions rolled forward or rolled back during recovery, you can examine the SQL Server error log and look at the recovery startup messages for each database:
2001-06-13 17:07:00.06 spid8 Starting up database 'pubs'. 2001-06-13 17:07:00.07 spid9 Starting up database 'Northwind'. 2001-06-13 17:07:00.07 spid10 Starting up database 'bigpubs2000'. 2001-06-13 17:07:01.85 spid5 Clearing tempdb database. 2001-06-13 17:07:02.07 server SQL server listening on 10.244.174.172: 1533. 2001-06-13 17:07:02.07 server SQL server listening on 127.0.0.1: 1533. 2001-06-13 17:07:04.86 spid8 48 transactions rolled forward in database 'pubs' (5). 2001-06-13 17:07:05.03 spid8 0 transactions rolled back in database 'pubs' (5). 2001-06-13 17:07:05.33 spid8 Recovery is checkpointing database 'pubs' (5) 2001-06-13 17:07:06.10 spid5 Starting up database 'tempdb'. 2001-06-13 17:07:06.96 spid3 Recovery complete.