matt.bowler (5/7/2012)
I do have one question: in the final demo where you looked at the log usage for an index rebuild, you mentioned "log reservation in case of rollback". Could you clarify what you mean by this?
Sure.
High level summary: If an operation is rolled back, SQL performs the undo by reading the transaction log and generating compensating operations to reverse the effects of whatever is being rolled back. Those operations, like any other data modification, get logged.
A rollback cannot be allowed to fail if say the rollback is caused by a full transaction log, that would result in the database being marked suspect, which is a very bad thing.
Hence, while SQL is performing any data modification and logging the changes, it also reserves enough space in the log to be able to log the undo of those operations. This is log reservation. The estimation that SQL uses is a very conservative one, it's better to reserve more log space than is actually needed than to not reserve enough and send the DB suspect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability