You can set a number of options for most databases. For the master database, you can only set one option, Truncate Log on Checkpoint.
The Edit Database window for that database appears.
The Options window appears.
When this option is on, you can perform the following nonlogged operations: use the nonlogged WRITETEXT and UPDATETEXT statements, use the SELECT INTO statement, or use fast bcp to do nonlogged loads.
If this option is set, tables with no indexes are loaded using bcp without logging the data changes in the transaction log. This results in faster loads. If this option is not set, or if the table has indexes, bcp in operations are logged.
You do not have to turn this option on to use SELECT INTO on a temporary table, because tempdb is never recoverable.
When the Select Into / Bulk Copy option is set, you are allowed to dump the transaction log only if you have not performed any nonlogged operations (select into, fast bulkcopy, or nonlogged WRITETEXT or UPDATETEXT statements) since the last database dump. If nonlogged operations have occurred since the last database dump, executing the DUMP TRANSACTION statement produces an error message instructing you to use DUMP DATABASE instead.
By default, the Select Into / Bulk Copy option is set to off in newly created databases. To change the default, set this option in the model database.
The No Checkpoint on Recovery option is useful when an up-to-date copy of a database is kept. In these situations, there are a "primary" and a "secondary" database. Initially, the primary database is dumped and loaded into the secondary database. Then, at intervals, the transaction log of the primary database is dumped and loaded into the secondary database. If this option is on (selected) in the secondary database, no checkpoint record is added to a database after it is recovered, so that subsequent transaction log dumps from the primary database can be loaded into it.
It can be useful to set this option to on while doing development work during which backups of the transaction log are not needed. If this option is off (the default condition) and the transaction log is never dumped, the transaction log continues to grow, and you can run out of space in your database.
While the Truncate Log on Checkpoint option is on, you cannot dump the transaction log because the log is being truncated, and changes therefore are not recoverable from transaction log dumps. In this situation, executing the DUMP TRANSACTION statement produces an error message instructing you to use DUMP DATABASE instead.
By default, the Truncate Log on Checkpoint option is set to off in newly created databases. To change the default, set this option in the model database. The Truncate Log on Checkpoint option is set to on by default for the master database. It is advised that you leave this value set to on to avoid filling up the transaction log for the master database. Since you cannot dump only the transaction log for the master database, there is no benefit in changing this value.