Setting Database Options

You can set a number of options for most databases. For the master database, you can only set one option, Truncate Log on Checkpoint.

    To set database options

1. From the Server Manager window, select a server, and then open that server's Databases folder and double-click a database name.

   The Edit Database window for that database appears.

2. Choose the Options tab.

   The Options window appears.

   

3. Select or clear the check boxes for each of the options.

   Select Into / Bulk Copy

   When this option is selected, nonlogged operations can be performed.

   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.

   DBO Use Only

   When this option is selected, only the database owner can access the database. (This status is indicated by the database icon in the Server Manager window. Choose Server Manager's Display Legend button for more information.)

   No Checkpoint on Recovery

   Defines whether or not a checkpoint record is added to the database after it is recovered during a SQL Server startup. If this option is off (cleared)¾the default condition¾a checkpoint record is added.

   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.

   Read Only

   When this option is selected, users can view the contents of a database but cannot modify data. (This status is indicated by the database icon in the Server Manager window. Choose Server Manager's Display Legend button for more information.)

   Single User

   When this option is selected, only one user at a time can access the database. When this option is selected, the Truncate Log on Checkpoint option is not supported. Truncate the log after single user operations are completed. (This status is indicated by the database icon in the Server Manager window. Choose Server Manager's Display Legend button for more information.)

   Columns Null by Default

   Selecting this option is the equivalent of setting sp_dboption ANSI null default option to TRUE, which changes the database default Nullability to NULL. For more information¾including a discussion of nullability¾see the Microsoft SQL Server Transact-SQL Reference.

   Truncate Log on Checkpoint

   When this option is selected, the transaction log is truncated (committed transactions are removed) every time the CHECKPOINT process occurs (usually about once per minute).

   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.

4. Choose OK.