INF: SQL Server Agent Automatically Sets "Trunc. Log on Chkpt" for MSDB (257856)

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
					
```