checkpoint frequency

is there a way to know as to how frequently a chkpt is being issued i.e., is there a system table where this information is logged. i believe it is not a fixed interval and is determined by sql sqlserve based on the system activity and the recovery interval configuration, which specifies themaximum acceptable recovery time.

sajalkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jbiswasCommented:
The automatic checkpoint interval is calculated by SQL Server on the basis of system activity and the recovery interval value in the "syscurconfigs" system table. The "recovery interval" determines checkpoint frequency by specifying the amount of time it should take the system to recover. Reset this value by executing the sp_configure system stored procedure and the RECONFIGURE statement.
0
sajalkAuthor Commented:
the answer does not answer my question. you have essentially said what i said in the question.
since chkpt frequency is determined by the system activity and recovery interval settings, i would not expect chkpt to occur at regular fixed interval.
my question was as to where i can look up to as to which times a chkpt was issued.
0
TMSCommented:
You are right, this is not fixed, the 'recovery interval configuration option' is just a MAXIMUM number, so the interval is up to that figure. I'm pretty sure the real number is kept hidden in an internal variable of SQL server. It is not stored anywhere, and I guess it's not available to the 'outside' world.

You'd have to ask to an SQL server designer to know the answer (which I'm sure is NO)...
May I ask why you want to know that ?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

sajalkAuthor Commented:
my curiousity was essentially academic.
thanx.
sajal.
0
bretCommented:
I'm basing this answer on Sybase SQL Server, so I can't gaurantee it works on MS-SQL Server, but there is a good chance it will.

If you execute:

dbcc traceon(3604)
go
dbcc log(<dbid>,0,0,0,-10000,17)
go

The server will print out up to the most recent 10,000 log
records from the log.  These records don't include a datetime
stamp, but you could determine how many checkpoints were being issued per hour by running this hourly and determining how many
new checkpoint records were issued.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sajalkAuthor Commented:
bretts's knowledge is impressive.
thanx.
sajal.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.