May 15, 2017 at 10:43 am
Good Morning Experts,
In technet, it is mentioned that "Log records are written to disk when the transactions are committed.". So, this means log file contains only committed transactions. On checkpoint, transactions from log file are written to the mdf file. So, this means checkpoint only writes committed transactions to disk, correct? I read in Paul Randal's blog that checkpoint writes both committed and uncommitted transactions. Which one is correct? I am confused. Could you please explain
May 15, 2017 at 1:42 pm
coolchaitu - Monday, May 15, 2017 10:43 AMGood Morning Experts,In technet, it is mentioned that "Log records are written to disk when the transactions are committed.". So, this means log file contains only committed transactions. On checkpoint, transactions from log file are written to the mdf file. So, this means checkpoint only writes committed transactions to disk, correct? I read in Paul Randal's blog that checkpoint writes both committed and uncommitted transactions. Which one is correct? I am confused. Could you please explain
Not an expert but thought I'd chime in as the two articles don't really contradict each other.
"Log records are written to disk when the transactions are committed" does not mean it only has committed transactions.It means the log needs to know that a transaction has been committed. The log having committed and uncommitted transactions is part of write ahead logging - changes are written to the log first. The log needs to know what transactions committed and what transactions didn't for consistency - redo and undo operations. So commits need to add a log record. But that doesn't mean the log has only committed transactions - it's just recording that the commit took place.
Checkpoint writes dirty pages in memory to disk - it doesn't matter whether the pages involve a committed or uncommitted transaction it's just writing dirty pages (except for tempdb which works differently). Just as the article on sqlskills says, pages can be dirty from committed or uncommitted transactions. Checkpoints and transactions are for the most part unrelated, or maybe independent of one another is better terminology.
Unfortunately, you can find things online about how checkpoints just write committed transactions to disk. Not sure how that one started, it's not accurate but you can find that quite often. Paul Randal's blog also addressed that one in his Myth A Day series:
A SQL Server DBA myth a day: (15/30) checkpoint only writes pages from committed transactions
Sue
May 15, 2017 at 11:18 pm
Sue_H - Monday, May 15, 2017 1:42 PMcoolchaitu - Monday, May 15, 2017 10:43 AMGood Morning Experts,In technet, it is mentioned that "Log records are written to disk when the transactions are committed.". So, this means log file contains only committed transactions. On checkpoint, transactions from log file are written to the mdf file. So, this means checkpoint only writes committed transactions to disk, correct? I read in Paul Randal's blog that checkpoint writes both committed and uncommitted transactions. Which one is correct? I am confused. Could you please explain
Not an expert but thought I'd chime in as the two articles don't really contradict each other.
"Log records are written to disk when the transactions are committed" does not mean it only has committed transactions.It means the log needs to know that a transaction has been committed. The log having committed and uncommitted transactions is part of write ahead logging - changes are written to the log first. The log needs to know what transactions committed and what transactions didn't for consistency - redo and undo operations. So commits need to add a log record. But that doesn't mean the log has only committed transactions - it's just recording that the commit took place.
Checkpoint writes dirty pages in memory to disk - it doesn't matter whether the pages involve a committed or uncommitted transaction it's just writing dirty pages (except for tempdb which works differently). Just as the article on sqlskills says, pages can be dirty from committed or uncommitted transactions. Checkpoints and transactions are for the most part unrelated, or maybe independent of one another is better terminology.
Unfortunately, you can find things online about how checkpoints just write committed transactions to disk. Not sure how that one started, it's not accurate but you can find that quite often. Paul Randal's blog also addressed that one in his Myth A Day series:
A SQL Server DBA myth a day: (15/30) checkpoint only writes pages from committed transactionsSue
Thanks a lot for replying Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply