Simple Recovery Model and Checkpoint

Question

Simple Recovery Model and Checkpoint

coolchaitu

SSChampion

Points: 10334
More actions
August 10, 2017 at 10:56 pm

#338685

Good morning Experts,
Checkpoint writes all dirty pages to disk. So, in simple recovery model, does checkpoint truncate only committed transactions or does it truncate both committed and uncommitted transactions

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Checkpoint marks VLFs that are not needed for anything (replication, transactions, full backups, etc) as reusable.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 2

GilaMonster - Friday, August 11, 2017 1:09 AM
Checkpoint marks VLFs that are not needed for anything (replication, transactions, full backups, etc) as reusable.

I did not understand Gail.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What, specifically, did you not understand in that statement?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 4

GilaMonster - Friday, August 11, 2017 3:30 AM
What, specifically, did you not understand in that statement?

does checkpoint truncate only committed transactions or does it truncate both committed and uncommitted transactions?

coolchaitu SSChampion Points: 10334 More actions · Answer 5

coolchaitu - Friday, August 11, 2017 8:34 AM
GilaMonster - Friday, August 11, 2017 3:30 AM
What, specifically, did you not understand in that statement?
does checkpoint truncate only committed transactions or does it truncate both committed and uncommitted transactions?

please answer if it truncates both or only committed....Both or only committed?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I answered that.
Before I waste time answering it again, please specify what, exactly, in my answer you did not understand?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 7

GilaMonster - Friday, August 11, 2017 10:12 AM
I answered that.
Before I waste time answering it again, please specify what, exactly, in my answer you did not understand?

Hi Gail,
Thanks for replying. From your answer, I am unable to understand if checkpoint truncates both or only committed transactions. Could you please help me understand

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

coolchaitu - Friday, August 11, 2017 10:20 AM
GilaMonster - Friday, August 11, 2017 10:12 AM
I answered that.
Before I waste time answering it again, please specify what, exactly, in my answer you did not understand?
Hi Gail,
Thanks for replying. From your answer, I am unable to understand if checkpoint truncates both or only committed transactions. Could you please help me understand

Checkpoint does neither. It marks virtual log files that are not needed for anything (replication, transactions, full backups, etc.) as reusable.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Ok, if you don't want to help me to help you understand.

Checkpoint marks VLFs, virtual log files, as reusable, if and only if they are not needed for database activity, including open transactions, database backups, replication, change data capture or a few other things.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

I think that's about as clear as it gets.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

coolchaitu SSChampion Points: 10334 More actions · Answer 11

GilaMonster - Friday, August 11, 2017 3:23 PM
Ok, if you don't want to help me to help you understand.
Checkpoint marks VLFs, virtual log files, as reusable, if and only if they are not needed for database activity, including open transactions, database backups, replication, change data capture or a few other things.

HI Gail,
According to BOL this is what CHECKPOINT does : "Writes all dirty pages for the current database to disk".
Does this mean checkpoint is writing from memory to mdf file? Please help me understand about the CHECKPOINT operation.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

coolchaitu - Friday, August 11, 2017 7:50 PM
"Writes all dirty pages for the current database to disk".
Does this mean checkpoint is writing from memory to mdf file?

Yup, exactly.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 13

GilaMonster - Saturday, August 12, 2017 3:59 AM
coolchaitu - Friday, August 11, 2017 7:50 PM
"Writes all dirty pages for the current database to disk".
Does this mean checkpoint is writing from memory to mdf file?
Yup, exactly.

Thanks a lot Gail