June 5, 2009 at 2:32 pm
I have some assumptions I am trying to confirm if anyone could help to correct me or confirm.
Assuming Full Recovery Mode
When an update/insert/delete Transaction is running it updates pages in RAM then upon Completition or Commit(depending on implicit or explicit transaction) writes to the Log file (LDF/DISK)
Upon CHECKPOINT The LDF is written to Data MDF.
IF The transaction is to large to fit in RAM it Will start writing to LDF/DISK before commit or completion?
Also
When a long update/insert/delete Transaction is running and killed or rolledback in the middle before completion or commit the rollback may take as long or longer than the transaction time.
This part I do not understand. If the transaction is killed in the middle it has not been commited and therefore should not be written to Data(mdf) so why the long rollback time. Why not just mark the transaction as defunct in the log(ldf) and not apply the changes and return immediately.
Why does it playback through the reverse of the changes? What is it actually doing?
Thanks for your help.
Mark
June 5, 2009 at 2:51 pm
ganci.mark (6/5/2009)
Assuming Full Recovery Mode
All recovery modes behave the same. Recovery mode affects how long a transaction remains in the log and, in the case of bulk operations, how much is logged. Recovery models do not affect how transactions run.
When an update/insert/delete Transaction is running it updates pages in RAM then upon Completition or Commit(depending on implicit or explicit transaction) writes to the Log file (LDF/DISK)
The log records must be hardened before the commit is considered complete, they may very well be written before. SQL maintains a log buffer in memory. It's nt very big. As soon as a transaction commits or the log buffer fills, that buffer is written to disk. Hence log records for other transactions that haven't yet committed may be written to disk as part of that.
Upon CHECKPOINT The LDF is written to Data MDF.
Upon checkpoint or before if there's memory pressure and the lazy writer kicks in. It is not a requirement for the transaction to have committed before the data changes are written to disk. The only requirement is log before data. A checkpoint that fires part way through a transaction will write data pages changed by that transaction to disk.
Also
When a long update/insert/delete Transaction is running and killed or rolled back in the middle before completion or commit the rollback may take as long or longer than the transaction time.
Yup, because SQL has to go an undo all the changes that it made. cannot doesn't just discard the active tran in memory as log records may have been written to disk and data changes may have been written to disk. It has to undo, and log the undo process as well in case there's a server crash half way through the undo.
Plus, even if the pages were only modified in memory, they still have to have the changes undone so that the next read operation to ask for those data pages gets the pages as they were before the transaction that was rolled back. A read done after a rollback must not see values that should have been rolled back.
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
June 8, 2009 at 9:27 am
Thank you Gail.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply