Microsoft KB Archive/815436

= INF: Use Trace Flag 3505 to Control SQL Server Checkpoint Behavior =

Article ID: 815436

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



SUMMARY
This article describes how to use trace flag 3505 to control the behavior of checkpoints.



MORE INFORMATION
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.

Improvements
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:

815056 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.

Usage Considerations
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.

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 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: sqlservr.exe –T3505   To enable the trace flag from isql, osql, or SQL Query Analyzer, run the following code: DBCC TRACEON( 3505 )   To disable the trace flag from isql, osql, or SQL Query Analyzer, run the following code: DBCC TRACEOFF( 3505 ) 

Note 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.

