What is the undo pass that SQL Server does during a RESTORE WITH STANDBY operation?

Question

It's my understanding that SQL Server generates undo files when applying a restore to a database using the RESTORE WITH STANDBY.

From the MSDN documentation (Emphasis mine):

The standby file is used to keep a "copy-on-write" pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY. The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores. After a RESTORE WITH STANDBY operation, the undo file is automatically deleted by the next RESTORE operation. If this standby file is manually deleted before the next RESTORE operation, then the entire database must be re-restored. While the database is in the STANDBY state, you should treat this standby file with the same care as any other database file. Unlike other database files, this file is only kept open by the Database Engine during active restore operations.

The standby_file_name specifies a standby file whose location is stored in the log of the database. If an existing file is using the specified name, the file is overwritten; otherwise, the Database Engine creates the file.

The size requirement of a given standby file depends on the volume of undo actions resulting from uncommitted transactions during the restore operation.

What is the undo pass referred to at the beginning?

From what I understand, those are the operations written in the log file that haven't been committed. If this is correct, why are those operations on the log file if they haven't been committed in the first place, and why does the RESTORE WITH STANDBY operation need to store them someplace to be able to bring the database up for read-only access (in other words, why can't they just be thrown away)?

Answer 1

why are those operations on the log file if they haven't been committed in the first place

All operations, committed or uncommitted, are written in the log. A commit ensures that all log entries are made durable (flushed to disk), but nothing prevents uncommitted entries from being flushed before that (either because a log block fills or because another transaction commits, thus forcing the flush for every transaction). A rollback must analyze all the transaction entries and generate compensating actions (for every insert do a delete, for every delete do an insert, for every update do an update that reverses the data back). These compensating actions are, of course, logged.

When a database is recovered it must rollback any transaction that is not committed in the log. It must as such analyze the log, figure out the uncommitted transactions, and then generate compensating actions for any all actions belonging to uncommitted transactions. Online recovery will write the compensation actions into the log itself. Standby recovery will write the compensating actions into an alternative stream, thus allowing for further log to be applied form a 'master' source later (this how log shipping standby read only access works).

Before asking any clarification question, please read ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging.

Answer 2

The undo pass is required to revert any dirty pages from in-flight transactions that were flushed to disk by a checkpoint operation.

A checkpoint writes ALL dirty pages from the buffer cache to disk, regardless of whether the transaction that generated them has been committed or not. Checkpoints and the Active Portion of the Log covers the gory details.

Restore WITH STANDBY allows you to bring the database up in a read-only state between restores (as your quote mentions). For the database to be in a transactionally consistent state, the undo pass has to be run. To continue further restores (which is the purpose of standby), the pages that were modified by UNDO will need to be re-applied, hence the need to store them in the standby file.