Microsoft KB Archive/257856

= INF: SQL Server Agent Automatically Sets "Trunc. Log on Chkpt" for MSDB =

Article ID: 257856

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q257856



SUMMARY
By default, the '''trunc. log on chkpt database option is set for the SQL Server msdb''' system database. This helps ensure that the transaction log of the database does not fill up, and prevents problems that may occur due to missing log space in the msdb system database. Because the msdb system database generally remains rather small, full database backups provide a fast alternative to transaction log backups for this database.

IMPORTANT: Although the '''trunc. log on chkpt option is set to True for the msdb database by default, it is possible for the administrator to switch it to False temporarily by using the SQL Enterprise Manager or the sp_dboption''' system stored procedure. However, the option only remains False until the next restart of the SQL Server Agent, when the '''trunc. log on chkpt''' is set to True again.

In SQL Server 2000, when you set database msdb recovery mode to full, it is changed back to simple recovery mode after restarting SQL Server Agent. It is still because SQL Server Agent sets '''trunc. log on chkpt''' upon startup.



MORE INFORMATION
The following steps can be used to reproduce the behavior:   Use the following Transact-SQL statements to set the '''trunc. Log on chkpt database option to False for the msdb''' database: use master go EXEC sp_dboption 'msdb', 'trunc. log on chkpt', 'false' go sp_dboption 'msdb' go   Use the following commands from a command prompt to stop and restart the SQL Server Agent service: net stop SQLServerAgent net start SQLServerAgent   Use the following statements to check the database options for the msdb database again: sp_dboption 'msdb' go 

Additional query words: truncate log on checkpoint

Keywords: kbinfo KB257856

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.