Microsoft SQL Server 2008 R2 Unleashed- P109:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1024 CHAPTER 31 Transaction Management and the Transaction Log 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 database or be redone if necessary. A transaction aborted during processing by an explicit rollback or a system error has its changes automatically undone. 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 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 be written to disk only 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 otherwise it could take an exceedingly long time for SQL Server to start up if it had to redo all the transactions contained in the transaction 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 whether any data changes
