Solved

checkpoint frequency

Posted on 1998-04-20
6
569 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
monitoring configuration for SQL server DB 32 50
SSIS GUID Variable 2 37
Parsing this XML works but the other one doesn't 9 30
Need to find substring in SQL 3 13
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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