Transaction Management

Question

what happens to uncommitted transactions that have started before the point of checkpoint. Do they have to redone since they have been interrupted by the checkpoint period? Just want to clarify any misconceptions.

Answer 1

Uncommitted transactions will not be affected by checkpoint,Below are the operations that occur in sequence when checkpoint occurs

1.Log record is written indicating starting of check point
2.All dirty pages irrespective of committed or uncommitted transactions are flushed to disk (this is done so as to minimize the effort that needs to be spend during crash recovery ) 3.Check point also keeps track of transactions which are active at the point of time and notes those lsn numbers
4.Log record indicating end of checkpoint is written

Assume below sequence of transactions

T1 started t2 started t3 started t1 completed checkpoint started checkpoint completed t3 completed

So if database is to crash now,crash recovery comes into picture and SQL starts analysis from last checkpoint and constructs dirty page table which consists of uncommitted transactions .
Assume if checkpoint is not there SQL has to read more transaction log and also SQL knows the pages affected by the transactions whether committed or uncommitted are persisted to disk,so during REDO phase it doesnt need to again roll forward the transactions into database and database can come online during REDO phase (enterprise edition only).I recommend further reading

https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

Uncommitted transactions are only rolledback if they dont complete before reading of database pages complete during backup
.More on this here by paul
http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/

Answer 2

Uncommitted transactions will not be effected by checkpoint. The checkpoint will occur, any dirty pages will be written to disk irrelevant of whether they have been committed or not.