Introducing Transaction Log Files
Each SQL Server 2000 database has at least one transaction log file and can have multiple transaction log files spread across a number of disks. Each transaction log file is a separate operating system file and is used by only one database. Each transaction log file generally has the .ldf filename extension (this extension is not required).
Each transaction log has a logical filename that is used in Transact-SQL statements and a physical filename that is used by the Windows operating system. Additional file properties include the file ID number, initial file size, file growth increment (if any), and maximum file size. Unlike data files that contain pages, ...view middle of the document...
When a database is created, the logical transaction log begins at the start of the first physical log file, which is also the beginning of the first virtual log file. The logical transaction log is made up of the portion of the transaction log that is required for recovery and backup. The portion of the transaction log required for recovery and backup will vary with the recovery model chosen. Backup and restore strategies are covered in Chapter 8.
How the Transaction Log Works
SQL Server 2000 uses a buffer cache, which is an in-memory structure, into which it retrieves data pages from disk for use by applications and users. Each modification to a data page is made to the copy of the page in the buffer cache. A modified buffer page in the cache that has not yet been written to disk is called a dirty page. The modification is recorded in the transaction log before it is written to disk. For this reason, the SQL Server 2000 transaction log is called a write-ahead transaction log. SQL Server 2000 has internal logic to ensure that a modification is recorded in the transaction log before the associated dirty page is written to disk. When SQL Server writes the dirty page in the cache to the disk, it is called flushing the page.
A transaction log record contains sufficient information to roll any database modification back or forward if necessary, including any extent allocations or index modifications. This ensures that any modification written to disk (such as a change to a data page or the creation of a new database object) can be rolled back in case the transaction that caused the modification fails to complete for any reason (such as a server failure or a rollback command), or rolled forward in case a completed transaction is not completely written to disk for any reason (such as a server failure).
| |Note |Because of this rollback capacity, a backup of the transaction log allows a database to be rebuilt when a drive |
| | |containing a data file fails. The rollback capacity is also the reason that the transaction log file for a database |
| | |should be on a different drive than the data file. |
SQL Server 2000 periodically writes dirty pages to disk from the buffer cache. These writes occur either when a database checkpoint process occurs or when an operating system thread (either an individual worker thread or a lazywriter thread) scans for dirty pages, writes the dirty pages to disk, and then clears space in the buffer cache to hold new data pages. Operating system threads may write dirty pages to disk before SQL Server 2000 knows whether the transaction is complete. However, if a transaction rolls back or never completes, the transaction log ensures that modifications made to disk by transactions that did not complete will be rolled back either via a rollback command or when the server restarts in...