Solved

checkpoint frequency

Posted on 1998-04-20
6
582 Views
Last Modified: 2012-05-04
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.

0
Comment
Question by:sajalk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091324
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
 

Author Comment

by:sajalk
ID: 1091325
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
 

Expert Comment

by:TMS
ID: 1091326
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:sajalk
ID: 1091327
my curiousity was essentially academic.
thanx.
sajal.
0
 
LVL 10

Accepted Solution

by:
bret earned 50 total points
ID: 1091328
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
 

Author Comment

by:sajalk
ID: 1091329
bretts's knowledge is impressive.
thanx.
sajal.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question