Article ID: 815436 - View products that this article applies to.
This article describes how to use trace flag 3505 to control the behavior of 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.
ImprovementsMicrosoft continues to make scalability improvements to the checkpoint process. For additional information about one scalability improvement that Microsoft has instituted, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/815056/EN-US/ )FIX: The Checkpoint Process Can Delay SQL Server Database Activity and Does Not Yield Scheduler Correctly Causing Error: 17883 to Occur
Because of these improvements, recovery interval adjustments and the use of trace flag 3505 are typically not necessary. Make sure that you have installed the latest version of SQL Server before you make any changes to the recovery interval.
When to Use Trace Flag 3505You can use trace flag 3505 to make sure that a checkpoint does not occur at a critical time for an application. You may want to control when the checkpoint occurs because the checkpoint may cause more I/O load on the system. SQL Server automatic checkpoints are designed to speed up disk writes and not to increase the I/O usage. However, in certain exceptional situations, the automatic checkpoints that SQL Server issues may affect the application performance because of the increased I/O load. Some applications may have strict response time requirements at specific times, and the impact of the checkpoint is significant. In these situations, you may want to control when the checkpoints occur.
If you experience these effects because of when the checkpoints occur, follow these steps:
Usage ConsiderationsSetting trace flag 3505 disables automatic checkpoints. Setting trace flag 3505 may increase recovery time and can prevent log space reuse until the next checkpoint is issued. Make sure to issue manual checkpoints on all read/write databases at appropriate time intervals.
Note Setting trace flag 3505 disables automatic checkpoints across the server for all databases. After you set trace flag 3505, you must issue checkpoint commands for all the databases where updates must occur. Trace flag 3505 does not prevent the internal checkpoints that are issued by certain commands, such as BACKUP.
Warning If you increase the checkpoint interval, the recovery work load and associated time increases. For some query patterns, the recovery time may be longer than the original transaction time. For example, if you set the recovery interval to 5 minutes, some query patterns can cause recovery times that are up to 15 minutes. This result may not be conducive to your overall system goals.
Like all other trace flags, trace flag 3505 may not be supported in future releases of SQL Server. Although you can set trace flag 3505 in later versions of SQL Server, it may not disable automatic checkpoints.
Use Trace Flag 3505You can set trace flag 3505 as a startup parameter or by using the isql command-line utility, the osql command-line utility, or SQL Query Analyzer:
For more information about the CHECKPOINT command in SQL Server, visit the following Microsoft Web site:
Article ID: 815436 - Last Review: October 3, 2003 - Revision: 3.4
Contact us for more help