Checkpoint Interval Setting

Question

Checkpoint Interval Setting

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
October 3, 2009 at 3:12 pm

#210372

in SQL Server 2005, how do we check what is the interval in which checkpoint happens. I am thinking in specific to a SIMPLE recovery model, to know how often does the truncation of inactive records happen?
Dan

Viewing 9 posts - 1 through 8 (of 8 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

from msdn:

If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

The log becomes 70 percent full.

The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

from microsoft and me:

it is NOT recommended to adjust the recovery interval

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

For a relatively complete discussion of when checkpoints are set read this:

http://technet.microsoft.com/en-us/library/ms189573(SQL.90).aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 3

George & BitBucket

Thanks for the 2 points & the reference link. Helpful info on Checkpoint.

mohd.sql Old Hand Points: 384 More actions · Answer 4

Hi friends,

I have real time issue running in my database....

i want to run checkpoint on one of my database where there shouldnt be a roll back while i start my database....

please help me out guys i have facing problem here.....

ashu:-D

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 5

U can use checkpoint, it will commit the data. There is no point of rollback. Are u getting any issues....??? Pls post here....

mohd.sql Old Hand Points: 384 More actions · Answer 6

how can i check the LSN.....if any commands plz....let me know.....

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 7

Jst fire checkpoint, and check whether there is any open transaction by using dbcc opentran, if there is no open transaction found, means there is no trxn is running, so there would be no chance to rollback the trxn.

mohd.sql Old Hand Points: 384 More actions · Answer 8

If i want to know the sequence of LSN in order and sequence of checkpoint occuring in order....what command shud i use to know the all LSN in order....??????