Checkpoints in sql server….. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Checkpoints in sql server…..

Hi How do we issue Checkpoints in the SQL Server 2000.If possible give me the Syntax for Explicit Checkpoint.When the Implicit Checkpoint occur. How to see the current checkpoint settings of the Database. Also, Please let me know whether I am going to face any problem while settings the
database for checkpoints. Very Very Urgent Please. Thanks raj
From BOL: Optimizing Server Performance Using I/O Configuration Options
The following server configuration option can be used to configure I/O usage and affect server performance: recovery interval
The recovery interval server configuration option controls when Microsoft® SQL Server™ 2000 issues a checkpoint in each database. By default, SQL Server determines the best time to perform checkpoint operations. However, to determine if this is the appropriate setting, monitor disk write activity on the database files using Windows NT Performance Monitor. Spikes of activity that cause disk utilization to reach 100 percent can affect performance. Changing this parameter to cause the checkpoint process to occur less often can improve overall performance in this situation. However, continue to monitor performance to determine if the new value has had a positive effect on performance. For more information, see recovery interval Option. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
SQL Server 2000 always generates automatic checkpoints. The interval between automatic checkpoints is based on the number of records in the log, not time. The time interval between automatic checkpoints can be highly variable. The time interval between automatic checkpoints is long if few modifications are made in the database. Automatic checkpoints occur frequently if a lot of data is modified. Refer to the books online for CHECKPOINT topic for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
quote:How do we issue Checkpoints in the SQL Server 2000.If possible give me the Syntax for Explicit Checkpoint.
It is possilble, syntax is:
Checkpoint
]]>