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.
Microsoft 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:
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 3505
You 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:
- Make sure that you have installed SQL Server 2000 Service Pack 3 (SP3) and any additional hotfixes.
- Review the I/O path to ensure maximum throughput.
- Review the database layout as it relates to the I/O paths.
Setting 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
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 3505
You 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:
- To set the trace flag as a startup parameter, run the following command from a command prompt:
- To enable the trace flag from isql, osql, or SQL Query Analyzer, run the following code:
- To disable the trace flag from isql, osql, or SQL Query Analyzer, run the following code:
You can set trace flag 3502 to include a message in the log at the beginning and ending of each checkpoint. This trace flag may be useful if you use trace flag 3505.