August 9, 2012 at 5:21 am
Hi Experts,
Sorry if this is noob.
1. 10 rows are updating and checkpoint happens when 5th row is updated.
As per my understanding the updated 5 rows are written to disk.
a.what happens if a rollback occurs before a commit??
2. Commit happened after a transaction and server crashed before a checkpoint what happens to the data?
Thanks in Advance
August 9, 2012 at 5:30 am
1. Checkpoint writes dirty pages to disk. Not individual rows.
a. Did not understand the question. Eighter rollback or commit can happen. Not both.
2. When the server starts up next time, database recovery (undo/redo) phase takes care of this transaction.
August 9, 2012 at 5:35 am
From reading, but this might be totally wrong.
As a checkpoint rights information relating to the transaction log as well as dirty pages to disk, if the transaction is not commited by the time the checkpoint occurs, the dirty pages are not written to disk.
So the pages which relate to the 10 rows are still marked as in transaction and dirty after the checkpoint.
But as I say I could be wrong.
August 9, 2012 at 6:20 am
Suresh B. (8/9/2012)
1. Checkpoint writes dirty pages to disk. Not individual rows.a. Did not understand the question. Eighter rollback or commit can happen. Not both.
2. When the server starts up next time, database recovery (undo/redo) phase takes care of this transaction.
Thanks Suresh for the quick reply
Sorry for not being precise
When data is being requested for Updation, the relevant pages (5 pages in my case )are loaded from disk into buffer cache .
Updation caused page to exceed ,page split occurs and now data is in 10 pages.
A.Transaction is open and out of 10 rows only 5 updated and remaining in memory to be modified checkpoint happened in the middle and those updated gets writen to disk.
1.What happenes if a rollback occurs just now?
Those in disk will be rollbacked to memory .Am i right?
B. update completed ,commit happens and server crashes.How the data is recovered?
C. When and how logging happens in log file ? Will it log each and everything when a transaction occurs into .ldf file or it will kep in buffer and checkpoint only moves those to .ldf file?
Thanks in Advance
August 9, 2012 at 11:52 pm
Ratheesh.K.Nair (8/9/2012)
1.What happenes if a rollback occurs just now?
Rollback happens in the memory and pages becomes dirty again.
Those in disk will be rollbacked to memory .Am i right?
Question of reading the pages from disk to memory does not araise. Because checkpoint does not remove the pages from memory after writing to disk.
B. update completed ,commit happens and server crashes.How the data is recovered?
Assuming that checkpoint has not happened. Data is recovered from the log.
C. When and how logging happens in log file ? Will it log each and everything when a transaction occurs into .ldf file or it will kep in buffer and checkpoint only moves those to .ldf file?
Log each and everything when a transaction occurs into .ldf file. That is why it is called Write Ahead Log (WAL).
Additional notes:
1) Checkpoint writes all dirty (comitted and uncommitted) pages to disk
2) Checkpoint does not remove the pages from buffer chache (RAM) after writting pages to disk.
August 10, 2012 at 5:47 am
anthony.green (8/9/2012)
As a checkpoint rights information relating to the transaction log as well as dirty pages to disk, if the transaction is not commited by the time the checkpoint occurs, the dirty pages are not written to disk.
Nope. Checkpoint writes all dirty data pages to disk regardless there's a transaction open or not.
If the transaction is then rolled back, SQL reads over the log and performs the compensating operations, just as it would if the checkpoint hadn't run. The presence or absence of the checkpoint has no effect on how the transaction is 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
August 10, 2012 at 5:49 am
GilaMonster (8/10/2012)
anthony.green (8/9/2012)
As a checkpoint rights information relating to the transaction log as well as dirty pages to disk, if the transaction is not commited by the time the checkpoint occurs, the dirty pages are not written to disk.Nope. Checkpoint writes all dirty data pages to disk regardless there's a transaction open or not.
If the transaction is then rolled back, SQL reads over the log and performs the compensating operations, just as it would if the checkpoint hadn't run. The presence or absence of the checkpoint has no effect on how the transaction is rolled back
Many thanks on the claification as always Gail.
August 10, 2012 at 6:13 am
Suresh B. (8/9/2012)
Ratheesh.K.Nair (8/9/2012)
1.What happenes if a rollback occurs just now?Rollback happens in the memory and pages becomes dirty again.
Those in disk will be rollbacked to memory .Am i right?
Question of reading the pages from disk to memory does not araise. Because checkpoint does not remove the pages from memory after writing to disk.
B. update completed ,commit happens and server crashes.How the data is recovered?
Assuming that checkpoint has not happened. Data is recovered from the log.
C. When and how logging happens in log file ? Will it log each and everything when a transaction occurs into .ldf file or it will kep in buffer and checkpoint only moves those to .ldf file?
Log each and everything when a transaction occurs into .ldf file. That is why it is called Write Ahead Log (WAL).
Additional notes:
1) Checkpoint writes all dirty (comitted and uncommitted) pages to disk
2) Checkpoint does not remove the pages from buffer chache (RAM) after writting pages to disk.
Thanks Suresh.
According to my understanding log file doesnt contain any data,then how is it possible to recover in above case.
I was under the impression that each and everything happens to a DB gets logged in .ldf file irrespective of checkpoint but was confused after reading a blog in which its mentioned that checkpoint writed data to .mdf & log informations to .ldf till then it will be in bufffer(then i because confused about recovery).
If you dont mind can you please clarify my doubt.
Thanks in Advance
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply