Solved

checkpoint frequency

Posted on 1998-04-20
6
529 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now