Setting Database Options

In this section, I will concentrate on how to set the various database options, as opposed to how the settings will affect your database. Many of the settings are fairly intuitively named, which helps, and are covered in detail elsewhere in this book. For example, a detailed explanation of the Recovery options can be found in Chapter 16, "Database Backup and Restore."

The Database Options

SQL Server has five categories of options that can be set to control database behavior. The categories and the associated values are listed in Table 11.1.

Using Enterprise Manager to Set Database Options

Many of the database options can be set right from the Options tab of the Database Properties page in Enterprise Manager, as shown in Figure 11.8.

The OFFLINE|ONLINE state option is also accessible in Enterprise Manager by right-clicking a database and selecting the All Tasks menu; OFFLINE or ONLINE appears, depending on the current state of the database.

Using T-SQL to Set Database Options

If you prefer to use T-SQL, or if the option you need to set doesn't appear in Enterprise Manager, you can use the ALTER DATABASE command to set options as well. The following example sets AUTO_SHRINK to OFF for the Credit database:

ALTER DATABASE Credit 
   SET AUTO_SHRINK OFF

The system-stored procedure sp_dboption is still available to check and set database options, but Microsoft advises it is available only for backward compatibility, and that you should use ALTER DATABASE instead. If you want to be a rebel and use it anyway, here is the syntax:

sp_dboption 'credit', 'autoshrink', 'false' 

An interesting note is that if you use Enterprise Manager to generate a database creation script from an existing database, it uses sp_dboption to set the database options. I guess somebody missed the "We advise against using this" memo.

Retrieving Option Information

The settings for database options are best retrieved using sp_helpdb or the DATABASEPROPERTYEX function. The sp_dboption stored procedure can be used to display option information as well, but remember: It's only around for backward compatibility.

The syntax for sp_helpdb is as follows:

sp_helpdb databasename 

The database options are listed, comma delimited, in the status column of the return. All Boolean options that are set to ON are returned, and all non-Boolean values are returned with the value to which they are set. Because the status column is a varchar (340), it can be difficult to view the option you are interested in. A more precise way of displaying the option information is with the DATABASEPROPERTYEX function. The function accepts input values for the database name and the property (options are properties), as shown here:

SELECT DATABASEPROPERTYEX ('Credit', 'IsAutoShrink') 

This function returns a value of 1 or 0 for Boolean values?with 1 being "on" or "true"?and returns the actual value for non-Booleans. Table 11.2 lists the valid properties.

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE Enabled
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

1 = TRUE
0 = FALSE
NULL = Invalid input

FULL
BULK LOGGED
SIMPLE

0 = Windows
collation
>0 = Sort order ID

ONLINE
OFFLINE
RESTORING
RECOVERING
SUSPECT

READ_ONLY
READ_WRITE

SINGLE_USER
RESTRICTED_ USER
MULTI_USER

Version number =
Database is open
NULL = Database is
closed