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
Solved

checkpoint frequency

Posted on 1998-04-20
6
558 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
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

808 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