Database Administration for Specific Platforms › SQL Server/Windows Routine Procedures › Truncating Transaction Log Guidelines › When to Use sp_dboption System Procedure with trunc. log on chkpt. Option

When to Use sp_dboption System Procedure with trunc. log on chkpt. Option

Setting the trunc. log on chkpt. option is a way to save space, however it means that you can recover the state of the database only as it was at the last database dump. Turning on the trunc. log on chkpt. option causes the transaction log to be automatically truncated whenever SQL Server initiates the checkpoint checking process (about every minute). The default is off. You can turn it on by issuing the following Management Studio dialog:

sp_dboption DBCSE, 'trunc. log on chkpt.', true

Bring the SQL Server down, using the SQL Server Manager icon in the SQL Server program group after issuing this statement, and then restart the server.

Note: Although Setting the trunc. log on chkpt. option saves space, it means that you can recover the database only as it was at the last database backup. It is usually better to have a background task regularly back up the transaction log.

If you want to restore only from the last DUMP DATABASE command issued against your database, then you may want to seriously consider use of the trunc. log on chkpt. db option. If so, you may want to back up using the DUMP DATABASE command more frequently than once a week.