564
Part V: Enterprise Data Management
Understanding logging and how it relates to recovery functions is critical for a successful backup
and recovery strategy. For an in-depth understanding of how logging works, check out an excellent
article for TechNet magazine by Paul Randal titled “Understanding Logging and Recovery in SQL Server.” The
article can be found online at http://technet.microsoft.com/en-us/magazine/2009.02
.logging.aspxTransaction Checkpoints
Understanding how SQL Server uses checkpoints in the transaction log is important
to understanding how the transaction log is backed up and emptied. Due to perfor-
mance reasons, every time a database page is modified in memory, it is not written
to disk immediately. SQL Server generates automatic checkpoints to write the dirty
database pages from memory to disk. The time interval between automatic check-
points is variable and depends on the amount of modifications made to the database
and the recovery interval SQL Server configuration option. Checkpoints calcu-
late the amount of work that must be done to recover the database during a system
restart.A checkpoint also occurs under any of the following conditions:■ When an ALTER DATABASE command is used.■ (^) When the SQL Server is shut down.
If you used the SHUTDOWN WITH NOWAIT command to shut down SQL Server, then SQL Server shuts down with-
out performing checkpoints in any database.
■ A minimally logged operation is performed in the database.
■ (^) A database backup is performed.
■ When an activity requiring database shutdown or database restart is performed.
■ (^) When the number of log entries exceeds the estimated amount of work required by
the SQL Server’s recovery interval confi guration option.
■ (^) If the database is in simple recovery model and the transaction log becomes 70
percent full.
Checkpoints may be manually initiated with a CHECKPOINT command. Checkpoints perform
the following activities:
■ (^) Marks the checkpoint spot in the transaction log
■ Writes a checkpoint-log record, including the following:
■ (^) The oldest active transaction
■ The oldest replication transaction that has not been replicated
c21.indd 564c21.indd 564 7/31/2012 9:22:50 AM7/31/2012 9:22:50 AM
http://www.it-ebooks.info