When does SQL Server execute a Checkpoint

Question

When does SQL Server execute a Checkpoint

YSLGuru

SSC-Insane

Points: 21661
More actions
July 11, 2008 at 11:13 am

#123771

We (my company) are looking into methods to track when changes are made to a particular table in our primary database (DB). One of the options being discussed is highly affected by Checkpoints because the DB's Recovery Model is set to SIMPLE.
I've looked thru BOL for SQL2K5 and the specifics I;ve found about Checkpoints reads to be somewhat in clonflict or at least unclear about when the system performs a Checkpoint. One BOL article states that when Recovery Mode = SIMPLE a checkpoint occurs when a backup is performed. It does not list any other times a checkpoint occurs giving the reader (at least me) the impression that this is the only time the system automatically performs a Checkpoint. I know that you can explicitly force a Checkpoint but what I'm looking for is when the system performs a Checkpoint and not so because a user has explicitly told it to issue a Checkpoint.
Another BOL entry lists several scenarios where a Checkpoint can occur but it does not incidcate if these are Recovery Model specific. between these 2 BOL help articles I'm uncertain as to when a Checkpoint can and will occur.
Can anyone either point to/reference a post or anything like it on the web or in BOL that in complete details when a Checkpoint will occurr when using the SIMPLE recovery model?
Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 11 posts - 1 through 10 (of 10 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Simple recovery does not just checkpoint when a backup is taken. Simple recovery mode really moves the pointer in the log file to the beginning of the file (effectively clearing the log file) every time a checkpoint happens.

The database will still checkpoint at the same rate that it would in full recovery mode. It just begins overwriting the log file when a checkpoint happens.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Ok, I have to ask, what possible method of tracking changes could you be using that would be impacted by when a checkpoint happens?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

check this info on recovery interval option

http://msdn.microsoft.com/en-us/library/ms191154.aspx

checkpoints occur for a number of reasons

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

checkpoints are roughly every few minutes. Not sure how this impacts what you're trying to do.

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

Michael Earl (7/11/2008)
Ok, I have to ask, what possible method of tracking changes could you be using that would be impacted by when a checkpoint happens?

Reading the active portion of the transaction log?

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

BOL implies this is done on a transactional basis not a time basis?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

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

Checkpoint occur at intervals based on the activity in the database and the specified recovery interval (default 1 min, I believe). The more activity, the more frequent the checkpoints.

Very simple, the recovery interval states how long the recovery of the database should (at most) take, in the case of a shutdown. Whenever SQL thinks that the number of transactions whose dirty pages have not been written to disk reached the point where it will take the allowable recovery time to recover, it will start a checkpoint to flush those pages to disk.

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

thats the way i understood it to be

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

JeremyE SSCoach Points: 16200 More actions · Answer 9

Just to add my 2¢, but a checkpoint will not clear the log of a replicated database until the Log Reader has run. If your database is replicated...

YSLGuru SSC-Insane Points: 21661 More actions · Answer 10

Thanks to all who responded. Since I first posted this, we have been forced (due to requirements of Microsoft's DPM software) to change from using the SIMPLE recovery model to using FULL and so the original question is no loger applicable.

Thanks again

Ed

Kindest Regards,

Just say No to Facebook!