Transaction Recovery

The row-level locking introduced in Microsoft® SQL Server™ version 7.0 required significant changes in the mechanisms used to roll back transactions.

Every SQL Server database has a transaction log that records data modifications made in the database. The log records the start and end of every transaction and associates each modification with a transaction. SQL Server stores enough information in the log to either redo (roll forward) or undo (roll back) the data modifications that make up a transaction. Each record recorded in the log is identified by a unique log sequence number (LSN). All of the log records for a transaction are chained together.

SQL Server records many different types of information in the transaction log. SQL Server 7.0 primarily logs the logical operations performed. The operation is reapplied to roll forward a modification, and the opposite of the logical operation is performed to roll back a modification.

SQL Server controls when modifications are written from its data buffers to disk. SQL Server may cache modifications in buffers for a period of time to optimize disk writes. A buffer page that contains modifications that have not yet written to disk is known as a dirty page. Writing a dirty buffer page to disk is called flushing the page. When modifications are cached, care must be taken to ensure that no data modification is flushed before the corresponding log image is written to the log file. This could create a modification that could not be rolled back if necessary. To ensure that it can recover all modifications, SQL Server uses a write-ahead log, which means that all log images are written to disk before the corresponding data modification.

A commit operation forces all log records for a transaction to the log file so that the transaction is fully recoverable even if the server is shut down. A commit operation does not have to force all the modified data pages to disk so long as all the log records are flushed to disk. A system recovery can roll the transaction forward or backward using only the log records.

Periodically, SQL Server ensures that all dirty log and data pages are flushed. This is called a checkpoint. Checkpoints reduce the time and resources needed to recover when a copy of SQL Server is restarted. In SQL Server 7.0, a checkpoint does not write out all dirty pages. Instead, a checkpoint:

• Writes to disk all dirty data pages that were recorded as dirty at the last checkpoint and have not been flushed after that time.
• Writes to disk a list of all outstanding transactions. This list contains the LSN of the first log record for the transaction.
• Writes to disk all dirty log pages.
• Records a list of all remaining dirty data pages. The LSN of the first log image for any of these dirty pages is recorded in the database boot page. This represents the point in the log at which a system-wide recovery must start processing the log and it is called the Minimum Recovery LSN.
• The LSN of the chain of log records that record the checkpoint is written to the database boot page.

This logic reduces the overhead of a checkpoint in SQL Server 7.0 compared to earlier versions of SQL Server. Because most dirty data pages will be written to disk in the interval from one checkpoint to the next, most checkpoints will have few modified data pages to write. Because all the log images are written to the log, all the information needed to recover the modifications is recorded on disk at a checkpoint.

Rolling Back an Individual Transaction

If any errors occur during a transaction, SQL Server uses the information in the log file to roll back the transaction. This rollback does not affect the work of any other users working in the database at the same time. Usually, the error is returned to the application, and if the error indicates a possible problem with the transaction, the application issues a ROLLBACK statement. Some errors, such as a 1205 deadlock error, roll back a transaction automatically. If anything stops the communication between the client and the server while a transaction is active, SQL Server rolls back the transaction automatically when notified of the stoppage by the network or operating system. This could happen if the client application terminates, if the client computer is shut down or restarted, or if the client network connection is broken. In all of these error conditions, any outstanding transaction is rolled back to protect the integrity of the database.

Recovery of All Outstanding Transactions at Start-up

It is possible for a SQL Server installation to sometimes stop processing; for example, if an operator restarts the server while users are connected and working in databases. This can create two problems:

• There may be an unknown number of SQL Server transactions partially completed at the time the server stopped. These incomplete transactions need to be rolled back.
• There may be an unknown number of data modifications recorded in the SQL Server log file, but the corresponding modified data pages were not flushed to the data files before the server stopped. Any committed modifications must be rolled forward.

Whenever a SQL Server installation is started, it must find out if either of these conditions exist and address them. The following steps are taken in each SQL Server database:

• The LSN of the last checkpoint is read from the database boot block along with the Minimum Recovery LSN.
• The transaction log is scanned from the Minimum Recovery LSN to the end of the log. All committed dirty pages are rolled forward by redoing the logical operation recorded in the log record.
• SQL Server then scans backward through the log file rolling back all uncompleted transactions by applying the opposite of the logical operation recorded in the log records.

This type of recovery is also done by a RESTORE statement. After restoring the copy of the database or log, the RESTORE statement also has to ensure that all log images recording dirty modifications are rolled forward and all uncompleted transactions are rolled back.