checkpoint settings 1

[ksbrace]

Hello, I'm fairly new to SQL Server, but have a decent amount of experience with Oracle. I am trying to figure out how often a checkpoint occurs and where I can change that frequency if I need to do so. thanks in advance,
Kelly

 

[ptheriault]

Here some info about checkpoints.

SQL Server automatically issues checkpoints to reduce the SQL Server recovery time and to permit log space reuse (truncation). In extremely rare situations, you must adjust the default checkpoint interval. You can set the default checkpoint interval by using the sp_configure stored procedure setting named recovery interval. You can use trace flag 3505 to adjust the interval by forcing the automatic checkpoint processing to be completely disabled.

Microsoft recommends that you retain the default setting for the recovery interval and leave the trace flag disabled unless you test extensively and initiate appropriate recovery planning. For high availability systems, such as clusters, Microsoft recommends that you do not change the recovery interval because it may affect data safety and availability.

If you use I/O mirroring and similar facilities, Microsoft recommends that you do not change the recovery interval. These systems are designed to work around the physical I/O layers. Increasing the checkpoint reduces the effectiveness of these designs.

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Also I should add that how frequently your instance checkpoint is directly related to how many transactions you are processing. I would not change the default checkpoint interval.

- Paul
- Database performance looks fine, it must be the Network!

 

[ksbrace]

paul,
Thanks for the info!