Managing the Database

In this section, I’ll discuss some of the options that can be configured at the database level. You’ll find additional coverage of these options in Chapter 7 where performance tuning is discussed.

Tweaking Database Configuration Options

To access the configuration options for a database, right-click on a database object and select Properties. You can also use the query sp_helpdb Northwind to view the properties of any database. In this section, I’ll use the Northwind database for most of the examples.

Restricting Access

In the database Properties dialog box, on the Options tab, you can place a database in single user mode, which restricts access to a single user. The user who issues the command keeps the only connection, and the connection is relinquished to the first requested connection thereafter. If there are any users connected to the database when you issue the command, the system displays an error.

If you want to disconnect the users through Enterprise Manager, go into the Management group, then use Current Activity to kill each user connection individually. This process is arduous, and generally by the time you have the users disconnected, an equally large number of users will have connected to the database.

This leads a crafty administrator to create scripts to perform this action. In SQL Server 2000, you now can do this through a simple command. To place your database in single user mode, issue the following command in Query Analyzer:

ALTER DATABASE Northwind SET SINGLE_USER 

SQL Server waits for all users to complete their current transactions and then disconnects them from the database. After all users are disconnected, the database is placed in single user mode. If a user tries to connect while this process is occurring, he receives the following error:

Server: Msg 952, Level 16, State 1, Line 1 Database 'Northwind' is in transition. Try the statement later.

You can also disconnect all the users immediately before placing the database in single user mode by using the ROLLBACK IMMEDIATE command. This disconnects all users except for the connection issuing the command. The syntax is

ALTER DATABASE Northwind SET SINGLE_USER with ROLLBACK IMMEDIATE

This command will seem quite abrupt to users who are in the middle of a transaction. The better choice of action is to allow users a given amount of time to finish their transactions. You can do this with the ROLLBACK AFTER X SECONDS command.

New users who attempt to connect during this period receive the same transition error (952). For example, to set your database to single user mode after a one-minute wait, use the following command:

ALTER DATABASE Northwind SET SINGLE_USER with ROLLBACK AFTER 60 SECONDS

Note 

These single user commands only work in SQL Server 2000 (in earlier versions you have to use the sp_dboption stored procedure).

After the database is in single user mode, you can use the SET command with the MULTI_USER clause to return the database to its original state. If any additional users attempt to connect to the database while it’s in single user mode, they receive the following error:

Server: Msg 924, Level 14, State 1, Line 1 Database 'Northwind' is already open and can only have one user at a time. 

Several database maintenance commands require that your database be in single user mode before they can execute. Additionally, some DBCC maintenance commands will run faster while the database is in single user mode. Single user mode is also helpful when you’re trying to debug a problem with a database and want stable data that’s not in flux with constant updates.

Placing your database in read-only mode prevents users from writing data to the database. Your database can obtain slight performance gains by setting it to read-only mode. After you place a database in read-only mode, no locks are required, since SQL Server doesn’t have to guarantee data integrity against inserts and updates. You can set the database to read-only mode in Enterprise Manager under the Database Options tab or use the following syntax in Query Analyzer:

ALTER DATABASE Northwind SET READ_ONLY with ROLLBACK IMMEDIATE

After issuing this command, transactions are rolled back and the database is placed in read-only mode. There are also some other side effects to placing a database in this mode:

• Users can no longer update or insert data into the database.

• The database can no longer shrink.

• Recovery at startup is skipped because there are no transactions to roll back.

To take a database out of read-only mode, specify the READ_WRITE keyword in the ALTER DATABASE query. If you have an active reporting database where the data is refreshed nightly, consider creating the read-only tables in a separate database and making it read-only.

Torn Page Detection

Torn page detection is turned on by default to prevent certain types of corruption in your database. When this option is turned on, SQL Server inspects your data pages as they are read from the database. If an inconsistency is detected, the database is marked as corrupt and you must restore from the last known good backup. To set this option, use the following syntax:

ALTER DATABASE Northwind SET TORN_PAGE_DETECTION ON 

This option is turned on by default and should be left on. It causes slight unnoticeable performance degradation, but that’s a small price to pay to avoid a corrupt database.

Keep in mind that turning this option on will not actually prevent the corruption. If you don’t set this option, it may take much longer to find the corruption and may be too late to restore the database. SQL Server rarely has corrupted databases since 7.0, but when they occur it is typically because of a power failure. You can eliminate this problem most of the time by having the server attached to a backup power supply or UPS attached.

Recovery Models

Administrators who are familiar with previous versions of SQL Server will wonder where the Truncate On Checkpoint and Select Into/Bulk Copy options went on the database Options tab. These options have actually been consolidated into the new recovery model scheme that SQL Server 2000 uses.

However, the older options have been preserved for backward compatibility. For example, you can set the Truncate On Checkpoint option by using the following syntax:

sp_dboption Northwind ,'Trunc. Log on Chkpt.', true

As soon as you do this, though, SQL Server will change the recovery mode. Table 2-1 describes the various recovery models as well as how each model was achieved in SQL Server 7.0.

Table 2-1: SQL Server Recovery Models

Recovery Model	Old Options	Description
Simple	Truncate On Checkpoint: On Select Into/Bulkcopy: On or Off	The easiest to administer, but only allows you to perform complete and differential backups. No transaction logs are kept or can be backed up.
Full	Truncate On Checkpoint: Off Select Into/Bulkcopy: Off	The most difficult to administer, but offers the best point-in-time recovery. All transactions are logged, including bulk operations, and all backup options are available.
Bulk-Logged	Truncate On Checkpoint: Off Select Into/Bulkcopy: On	A slight compromise to the Full recovery model. It offers the advantages of the Full model without fully logging bulk operations such as BCP and index creations directly. When you perform a transaction log backup, it will back up the transaction log plus the pieces of the database that have been bulk loaded. Your transaction log backups can grow rather large using this method, but your transaction log will remain smaller.

Setting this option is easy. Use the ALTER DATABASE command with the following syntax to set your database to the Full recovery model:

ALTER DATABASE Northwind SET RECOVERY FULL

Tip 

To determine the recovery model you’re currently using programmatically, run the sp_helpdb option in Query Analyzer. You can also use the following query to determine just the database property: SELECT DATABASEPROPERTYEX(‘Northwind’ , ‘recovery’).

(More about the advantages of each of these models in Chapter 8.)

Auto Shrink

The Auto Shrink option on the database Options tab enables SQL Server to poll the database and log files periodically to see if they could be shrunk. If your database is in Simple recovery mode, the transaction log is shrunk after each checkpoint, since it is not needed. To turn on this setting, simply check the box in the database Options tab or use the following syntax:

ALTER DATABASE Northwind SET AUTO_SHRINK ON

This option is turned on by default in Desktop Edition server and turned off in all other editions. I recommend that you turn this option off for any database in production or in any environment where you want to have higher performance.

Caution 

Turning this option on can lead to worse database performance, as the database shrinks during production hours. Before the database can be shrunk, SQL Server creates locks as it moves data from page to page, causing users to timeout or deadlock. If you want to shrink your database in production, you should schedule a job to shrink your database during off-peak hours.

Taking Databases Offline

Another option that is not available through the database Properties dialog is the Offline option. You can now set this option in Enterprise Manager by right-clicking the database and selecting All Tasks | Take Offline from the shortcut menu. To perform this task through T-SQL, connect to the master database and use the ALTER DATABASE command as follows:

ALTER DATABASE Northwind SET OFFLINE

Once the database is offline, no one is allowed to connect to the database. You can then copy the files and attach them to other servers. If a user tries to connect to the database while it’s offline, the user receives the following error message:

Server: Msg 942, Level 14, State 4, Line 1 Database 'Northwind2' cannot be opened because it is offline.

If a database on your server is offline and you attempt to view the Login Properties on the Security tab, you’ll receive an error.

Note 

You can still modify all the properties for the login, except for the properties of the offline database.

To get the database back online, simply use the SET ONLINE command as shown here:

ALTER DATABASE Northwind SET ONLINE

Caution 

Once a database is offline, your maintenance plans that backup and perform maintenance on your entire server will appear to fail. It will not harm the integrity of the entire maintenance plan, but instead will fail on the offline database or databases. If any one piece of a maintenance plan fails, the entire maintenance plan will appear to fail even if the other databases passed and processed their backups.

Compatibility Levels

To preserve backward compatibility, SQL Server provides compatibility levels at the database level. Compatibility levels allow you to specify that you’d like the database to exhibit the behavior of a specific version of a database.

This is a workaround for coping with minor changes in the behavior of some queries from SQL Server 6.5 to 2000. For example, if you run the following query in SQL Server 6.5, it returns 1:

SELECT DATALENGTH('')

The same query in SQL Server 7.0 or 2000 returns 0. This is because SQL Server 2000 and 7.0 treat the string as truly empty. In SQL Server 6.5, empty strings were treated as if they were one character in length.

You can use the Compatibility Level setting option when you upgrade your server to a new version, if you find abnormalities in your queries (you don’t receive the results you’re expecting). Set the option to the previous level and then debug your queries. After your queries are adjusted to SQL Server 2000, you can set the compatibility level back to 2000 (80).

Auto Close

When this option is turned on, SQL Server shuts down a database and frees up the resources after all users have disconnected. This option is turned on by default in Desktop Edition, but turned off by default in every other edition.

Turning on this option frees up resources, but it also takes up resources when it opens and closes the database. This option should be turned off in production to avoid the overhead caused by constant database opening. This option is good for Desktop Edition, because the workstation won’t have nearly the amount of connections that a production server has. To turn the option on, use the following command:

ALTER DATABASE Northwind SET AUTO_CLOSE ON

Auto Create Statistics

Statistics in SQL Server store vital information about the key values in a column. This provides SQL Server with the necessary information to generate an efficient execution plan. Statistics are found in the sysindexes table in a binary column called statblob.

When statistics get old, performance suffers. There is a slight performance hit when this option is turned on, but it’s well worth it. Any DBA who had to experience updating statistics constantly in 6.5 can appreciate this option. This option is turned on by default, but it can be adjusted manually by using the following syntax:

ALTER DATABASE Northwind Set AUTO_CREATE_STATISTICS ON

Note 

Turning on this option does have a small performance impact on production. I’ve seen very few situations where you’d want to turn this option off, though. Watch Profiler to see if an unnecessary amount of recompiles are occurring. In the rare event that SQL Server does not recompile your indexes in a timely manner, it will affect the performance of your server and you may want to consider scheduling the recompile as a job by executing UPDATE STATISTICS.