Setting Database Options

You can set several dozen options, or properties, for a database to control certain behavior within that database. Some options must be set to ON or OFF, some must be set to one of a list of possible values, and others are enabled by just specifying their name. By default, all of the options that require ON or OFF have an initial value of OFF unless the option was set to ON in the model database. All databases created after an option is changed in model will have the same values as model. You can easily change the value of some of these options by using SQL Server Management Studio. You can set all of them directly by using the ALTER DATABASE command. (You can also use the sp_dboption system stored procedure to set some of the options, but that procedure is provided for backward compatibility only and is scheduled to be removed in a future version of SQL Server.)

Examining the sys.databases catalog view can show you the values of all the options that have been set. The procedure also returns other useful information, such as database ID, creation date, and the Security ID (SID) of the database owner. The following query retrieves some of the most important columns from sys.databases for the four databases that exist on a new default installation of SQL Server.

SELECT name, database_id, suser_sname(owner_sid) as owner ,      create_date, user_access_desc, state_desc FROM sys.databases WHERE database_id <= 4;

The query produces this output, although the created dates may vary:

name   database_id owner create_date             user_access_desc state_desc ------ ----------- ----- ----------------------- ---------------- ---------- master 1           sa    2003-04-08 09:13:36.390 MULTI_USER       ONLINE tempdb 2           sa    2006-05-27 12:02:35.327 MULTI_USER       ONLINE model  3           sa    2003-04-08 09:13:36.390 MULTI_USER       ONLINE msdb   4           sa    2005-10-14 01:54:05.240 MULTI_USER       ONLINE

The sys.databases view actually contains both a number and a name for both the user_access and state information. Selecting all the columns from sys.databases would show you that the user_access_desc value of MULTI_USER has a corresponding user_access value of 0, and the state_desc value of ONLINE has a state value of 0. Books Online shows the complete list of number and name relationships for the columns in sys.databases. These are just two of the database options displayed in the sys.databases view. The complete list of database options is divided into seven main categories: state options, cursor options, auto options, parameterization options, SQL options, database recovery options, and external access options. There are also options for specific technologies SQL Server can participate in, including database mirroring, Service Broker activities, and snapshot isolation. Some of the options, particularly the SQL options, have corresponding SET options that you can turn on or off for a particular connection. Be aware that the ODBC or OLE DB drivers turn on a number of these SET options by default, so applications will act as if the corresponding database option has already been set.

Here is a list of the options, by category. Options listed on a single line and values separated by vertical bars (|) are mutually exclusive.

State options

SINGLE_USER | RESTRICTED_USER | MULTI_USER

OFFLINE | ONLINE | EMERGENCY

READ_ONLY | READ_WRITE

Cursor options

CURSOR_CLOSE_ON_COMMIT { ON | OFF }

CURSOR_DEFAULT { LOCAL | GLOBAL }

Auto options

AUTO_CLOSE { ON | OFF }

AUTO_CREATE_STATISTICS { ON | OFF }

AUTO_SHRINK { ON | OFF }

AUTO_UPDATE_STATISTICS { ON | OFF }

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

Parameterization options

DATE_CORRELATION_OPTIMIZATION { ON | OFF }

PARAMETERIZATION { SIMPLE | FORCED }

SQL options

ANSI_NULL_DEFAULT { ON | OFF }

ANSI_NULLS { ON | OFF }

ANSI_PADDING { ON | OFF }

ANSI_WARNINGS { ON | OFF }

ARITHABORT { ON | OFF }

CONCAT_NULL_YIELDS_NULL { ON | OFF }

NUMERIC_ROUNDABORT { ON | OFF }

QUOTED_IDENTIFIER { ON | OFF }

RECURSIVE_TRIGGERS { ON | OFF }

Database recovery options

RECOVERY { FULL | BULK_LOGGED | SIMPLE }

TORN_PAGE_DETECTION { ON | OFF }

PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }

External access options

DB_CHAINING { ON | OFF }

TRUSTWORTHY { ON | OFF }

Database mirroring options

PARTNER { = 'partner_server'

| FAILOVER

| FORCE_SERVICE_ALLOW_DATA_LOSS

| OFF

| RESUME

| SAFETY { FULL | OFF }

| SUSPEND

| TIMEOUT integer

}

WITNESS { = 'witness_server' |

OFF

}

Service Broker options

ENABLE_BROKER | DISABLE_BROKER

NEW_BROKER

ERROR_BROKER_CONVERSATIONS

Snapshot Isolation options

ALLOW_SNAPSHOT_ISOLATION {ON | OFF }

READ_COMMITTED_SNAPSHOT {ON | OFF } [ WITH <termination> ]

State Options

The state options control who can use the database and for what operations. There are three aspects to usability: The user access state determines which users can use the database, the status state determines whether the database is available to anybody for use, and the updateability state determines what operations can be performed on the database. You control each of these aspects by using the ALTER DATABASE command to enable an option for the database. None of the state options uses the keywords ON and OFF to control the state value.

SINGLE_USER | RESTRICTED_USER | MULTI_USER

These three options describe the user access property of a database. They are mutually exclusive; setting any one of them unsets the others. To set one of these options for your database, you just use the option name. For example, to set the AdventureWorks database to single-user mode, use the following code:

ALTER DATABASE AdventureWorks SET SINGLE_USER;

A database in SINGLE_USER mode can have only one connection at a time. A database in RESTRICTED_USER mode can have connections only from users who are considered "qualified"those who are members of the dbcreator or sysadmin server role or the db_owner role for that database. The default for a database is MULTI_USER mode, which means anyone with a valid user name in the database can connect to it. If you attempt to change a database's state to a mode that is incompatible with the current conditionsfor example, if you try to change the database to SINGLE_USER mode when other connections existthe behavior of SQL Server will be determined by the TERMINATION option you specify. I'll discuss termination options shortly.

To determine which user access value is set for a database, you can examine the sys.databases catalog view, as shown here:

SELECT USER_ACCESS_DESC FROM sys.databases WHERE name = '<name of database>';

This query will return one of MULTI_USER, SINGLE_USER or RESTRICTED_USER.

OFFLINE | ONLINE | EMERGENCY

You use these three options to describe the status of a database. They are mutually exclusive. The default for a database is ONLINE. As with the user access options, when you use ALTER DATABASE to put the database in one of these modes, you don't specify a value of ON or OFFyou just use the name of the option. When a database is set to OFFLINE, it is closed and shut down cleanly and marked as offline. Any snapshots for the data are automatically dropped. The database cannot be modified while the database is offline. A database cannot be put into OFFLINE mode if there are any connections in the database. Whether SQL Server waits for the other connections to terminate or generates an error message is determined by the TERMINATION option specified.

The following code examples show how to set a database's status value to OFFLINE and how to determine the status of a database:

ALTER DATABASE AdventureWorks SET OFFLINE; SELECT state_desc from sys.databases WHERE name = 'AdventureWorks';

A database can be explicitly set to EMERGENCY mode, and I'll explain why you might want to do that after I discuss the database status values that cannot be set.

As shown in the preceding query, you can determine the current status of a database by examining the state_desc column of the sys.databases view. This column can return status values other than OFFLINE, ONLINE, and EMERGENCY, but those values are not directly settable using ALTER DATABASE. A database can have the status value RESTORING while it is in the process of being restored from a backup. It can have the status value RECOVERING during a restart of SQL Server. The restore process is done on one database at a time, and until SQL Server has finished restoring a database, the database has a status of RECOVERING. If the recovery process cannot be completed for some reason (most likely because one or more of the log files for the database is unavailable or unreadable), SQL Server gives the database the status of RECOVERY_PENDING. Your databases can also be put into RECOVERY_PENDING mode if SQL Server runs out of either log or data space during rollback recovery, or if SQL Server runs out of locks or memory during any part of the startup process. I'll go into more detail about the difference between rollback recovery and startup recovery in Chapter 5.

If all the needed resources, including the log files, are available, but corruption is detected during recovery, the database may be put in the SUSPECT state. You can determine the state value by looking at the state_desc column in the sys.databases view. A database is completely unavailable if it's in the SUSPECT state, and you will not even see the database listed if you run sp_helpdb. However, you can look at the DATABASEPROPERTYEX values of a suspect database and see its status in the sys.databases view. In many cases, you can make a suspect database available for read-only operations by setting its status to EMERGENCY mode. If you really have lost one or more of the log files for a database, EMERGENCY mode allows you to access the data while you copy it to a new location. When you move from RECOVERY_PENDING to EMERGENCY, SQL Server shuts down the database and then restarts it with a special flag that allows it to skip the recovery process. Skipping recovery can mean you have logically or physically inconsistent datamissing index rows, broken page links, or incorrect metadata pointers. By specifically putting your database in EMERGENCY mode, you are acknowledging that the data might be inconsistent but that you want access to it anyway.

Emergency Mode Repair

You can run the DBCC CHECKDB command while in EMERGENCY mode, and when you specify the REPAIR_ALLOW_DATA_LOSS option, SQL Server can perform some special repairs on the database, which may allow for ordinarily unrecoverable databases to be made physically consistent and brought back online. These repairs should be used as a last resort and only when you cannot restore the database from a backup.

When the database is set to EMERGENCY mode, the database is internally set to READ_ONLY, logging is disabled, and access is limited to members of the sysadmin role.

However, the properties of the database that you see in sys.databases will not reflect these restrictions.

When the database is in emergency mode and DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause is run, the following actions are taken:

• DBCC CHECKDB uses pages that have been marked inaccessible because of I/O or checksum errors, as if the errors have not occurred in order to increase the chances for data recovery.

• DBCC CHECKDB attempts to recover the database using regular log-based recovery techniques.

• If database recovery is unsuccessful, the transaction log is rebuilt. Rebuilding the transaction log may result in the loss of transactional consistency.

If the DBCC CHECKDB command succeeds, the database is in a physically consistent state and the database status is set to ONLINE. However, the database may contain one or more transactional or logical inconsistencies. You should consider running DBCC CHECKCONSTRAINTS to identify any business logic flaws and immediately back up the database.

If the DBCC CHECKDB command fails, the database cannot be repaired.

In some cases, EMERGENCY mode is not possible, in particular if some of the metadata related to space allocation, which is needed to start up the database, is missing or corrupt.

You can attempt to set a database that is in EMERGENCY mode into ONLINE mode (if the missing files have been made available, for example), and SQL Server will try to run recovery on the database. If the transition to ONLINE cannot be completed, the database will be left in either RECOVERY_PENDING or SUSPECT status, just like when you first bring up your SQL Server instance and try to recover the database. Once again, you can change the state of the RECOVERY_PENDING database to EMERGENCY mode to allow the data to be read.

It's relatively easy to test emergency status value for a database on a test server. You can create a simple database with the three-word command CREATE DATABASE TESTDB, and then stop your SQL Server instance and rename (or remove) the log file. When you restart your instance, check the status of the new database:

SELECT name, database_id, user_access_desc, state_desc FROM sys.databases WHERE name = 'testdb';

The state_desc should show RECOVERY_PENDING, which you can now change to EMERGENCY:

ALTER DATABASE testdb SET EMERGENCY;

The database will now be available for reading data, even though there is no transaction log. If you try to update the database in any way, you'll get the following error:

Msg 3908, Level 16, State 1, Line 1 Could not run BEGIN TRANSACTION in database 'testdb' because the database is in bypass recovery mode. The statement has been terminated.

If you try to set the database state back to ONLINE, you will get an error indicating that recovery is not possible, and the database will be put back in RECOVERY_PENDING mode. As previously mentioned, running DBCC CHECKDB with the repair option while in EMERGENCY mode can put the database back in ONLINE mode if the database can be repaired.

READ_ONLY | READ_WRITE

These options describe the updatability of a database. They are mutually exclusive. The default for a database is READ_WRITE. As with the user access options, when you use ALTER DATABASE to put the database in one of these modes, you don't specify a value of ON or OFF, you just use the name of the option. When the database is in READ_WRITE mode, any user with the appropriate permissions can carry out data modification operations. In READ_ONLY mode, no INSERT, UPDATE, or DELETE operations can be executed. In addition, because no modifications are done when a database is in READ_ONLY mode, automatic recovery is not run on this database when SQL Server is restarted, and no locks need to be acquired during any SELECT operations. Shrinking a database in READ_ONLY mode is not possible.

A database cannot be put into READ_ONLY mode if there are any connections to the database. Whether SQL Server waits for the other connections to terminate or generates an error message is determined by the TERMINATION option specified.

The following code shows how to set a database's updatability value to READ_ONLY and how to determine the updatability of a database:

ALTER DATABASE AdventureWorks SET READ_ONLY; SELECT name, is_read_only FROM sys.databases WHERE name = 'AdventureWorks';

When READ_ONLY is enabled for database, the is_read_only column will return 1; otherwise, for a READ_WRITE database, it will return 0.

Termination Options

As I just mentioned, several of the state options cannot be set when a database is in use or when it is in use by an unqualified user. You can specify how SQL Server should handle this situation by indicating a termination option in the ALTER DATABASE command. You can have SQL Server wait for the situation to change, generate an error message, or terminate the connections of unqualified users. The termination option determines the behavior of SQL Server in the following situations:

• When you attempt to change a database to SINGLE_USER and it has more than one current connection

• When you attempt to change a database to RESTRICTED_USER and unqualified users are currently connected to it

• When you attempt to change a database to OFFLINE and there are current connections to it

• When you attempt to change a database to READ_ONLY and there are current connections to it

The default behavior of SQL Server in any of these situations is to wait indefinitely. The following TERMINATION options change this behavior:

• ROLLBACK AFTER integer [SECONDS] This option causes SQL Server to wait for the specified number of seconds and then break unqualified connections. Incomplete transactions are rolled back. When the transition is to SINGLE_USER mode, all connections are unqualified except the one issuing the ALTER DATABASE statement. When the transition is to RESTRICTED_USER mode, unqualified connections are those of users who are not members of the db_owner fixed database role or the dbcreator and sysadmin fixed server roles.

• ROLLBACK IMMEDIATE This option breaks unqualified connections immediately. All incomplete transactions are rolled back. Keep in mind that although the connection may be broken immediately, the rollback might take some time to complete. All work done by the transaction must be undone, so for certain operations, such as a batch update of millions of rows or a large index rebuild, you could be in for a long wait. Unqualified connections are the same as those described previously.

• NO_WAIT This option causes SQL Server to check for connections before attempting to change the database state and causes the ALTER DATABASE statement to fail if certain connections exist. If the database is being set to SINGLE_USER mode, the ALTER DATABASE statement fails if any other connections exist. If the transition is to RESTRICTED_USER mode, the ALTER DATABASE statement fails if any unqualified connections exist.

The following command changes the user access option of the AdventureWorks database to SINGLE_USER and generates an error if any other connections to the AdventureWorks database exist:

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH NO_WAIT;

Cursor Options

The cursor options control the behavior of server-side cursors that were defined using one of the following Transact-SQL commands for defining and manipulating cursors: DECLARE, OPEN, FETCH, CLOSE, and DEALLOCATE. Transact-SQL cursors are discussed in detail in Inside SQL Server 2005: TSQL Programming.

• CURSOR_CLOSE_ON_COMMIT {ON | OFF} When this option is set to ON, any open cursors are closed (in compliance with SQL-92) when a transaction is committed or rolled back. If OFF (the default) is specified, cursors remain open after a transaction is committed. Rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

• CURSOR_DEFAULT {LOCAL | GLOBAL} When this option is set to LOCAL and cursors aren't specified as GLOBAL when they are created, the scope of any cursor is local to the batch, stored procedure, or trigger in which it was created. The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or by a stored procedure output parameter. When this option is set to GLOBAL and cursors aren't specified as LOCAL when they are created, the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection.

Auto Options

The auto options affect actions that SQL Server might take automatically. All of these options are Boolean options, with a value of ON or OFF.

• AUTO_CLOSE When this option is set to ON, the database is closed and shut down cleanly when the last user of the database exits, thereby freeing any resources. All file handles are closed, and all in-memory structures are removed so that the database is not using any memory. When a user tries to use the database again, it reopens. If the database was shut down cleanly, the database isn't initialized (reopened) until a user tries to use the database the next time SQL Server is restarted. The AUTO_CLOSE option is handy for personal SQL Server databases because it allows you to manage database files as normal files. You can move them, copy them to make backups, or even e-mail them to other users. However, you shouldn't use this option for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will hurt performance.

• AUTO_SHRINK When this option is set to ON, all of a database's files are candidates for periodic shrinking. Both data files and log files can be automatically shrunk by SQL Server. The only way to free space in the log files so that they can be shrunk is to back up the transaction log or set the recovery mode to SIMPLE. The log files shrink at the point that the log is backed up or truncated.

• AUTO_CREATE_STATISTICS When this option is set to ON (the default), the SQL Server query optimizer creates statistics on columns referenced in a query's WHERE clause. Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query.

• AUTO_UPDATE_STATISTICS When this option is set to ON (the default), existing statistics are updated if the data in the tables has changed. SQL Server keeps a counter of the modifications made to a table and uses it to determine when statistics are outdated. When this option is set to OFF, existing statistics are not automatically updated. (They can be updated manually.) I'll discuss statistics in more detail in Chapter 7.

The preceding two statistics options, as well as AUTO_UPDATE_STATISTICS_ASYNC and the parameterization options DATE_CORRELATION_OPTIMIZATION and PARAMETERIZATION (all of which are new in SQL Server 2005), will be discussed in more detail in Inside SQL Server 2005: Query Optimization and Tuning.

SQL Options

The SQL options control how various SQL statements are interpreted. They are all Boolean options. The default for all these options is OFF for SQL Server, but many tools, such as the SQL Server Management Studio, and many programming interfaces, such as ODBC, enable certain session-level options that override the database options and make it appear as if the ON behavior is the default.

• ANSI_NULL_DEFAULT When this option is set to ON, columns comply with the ANSI SQL-92 rules for column nullability. That is, if you don't specifically indicate whether a column in a table allows NULL values, NULLs are allowed. When this option is set to OFF, newly created columns do not allow NULLs if no nullability constraint is specified.

• ANSI_NULLS When this option is set to ON, any comparisons with a NULL value result in UNKNOWN, as specified by the ANSI-92 standard. If this option is set to OFF, comparisons of non-Unicode values to NULL result in a value of TRUE if both values being compared are NULL.

• ANSI_PADDING When this option is set to ON, strings being compared with each other are set to the same length before the comparison takes place. When this option is OFF, no padding takes place.

• ANSI_WARNINGS When this option is set to ON, errors or warnings are issued when conditions such as division by zero or arithmetic overflow occur.

• ARITHABORT When this option is set to ON, a query is terminated when an arithmetic overflow or division-by-zero error is encountered during the execution of a query. When this option is OFF, the query returns NULL as the result of the operation.

• CONCAT_NULL_YIELDS_NULL When this option is set to ON, concatenating two strings results in a NULL string if either of the strings is NULL. When this option is set to OFF, a NULL string is treated as an empty (zero-length) string for the purposes of concatenation.

• NUMERIC_ROUNDABORT When this option is set to ON, an error is generated if an expression will result in loss of precision. When this option is OFF, the result is simply rounded. The setting of ARITHABORT determines the severity of the error. If ARITHABORT is OFF, only a warning is issued and the expression returns a NULL. If ARITHABORT is ON, an error is generated and no result is returned.

• QUOTED_IDENTIFIER When this option is set to ON, identifiers such as table and column names can be delimited by double quotation marks, and literals must then be delimited by single quotation marks. All strings delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers don't have to follow the Transact-SQL rules for identifiers when QUOTED_IDENTIFIER is ON. They can be keywords and can include characters not normally allowed in Transact-SQL identifiers, such as spaces and dashes. You can't use double quotation marks to delimit literal string expressions; you must use single quotation marks. If a single quotation mark is part of the literal string, it can be represented by two single quotation marks (''). This option must be set to ON if reserved keywords are used for object names in the database. When it is OFF, identifiers can't be in quotation marks and must follow all Transact-SQL rules for identifiers.

• RECURSIVE_TRIGGERS When this option is set to ON, triggers can fire recursively, either directly or indirectly. Indirect recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire, thereby causing an update to occur on the original table, which causes the original trigger to fire again. For example, an application updates table T1, which causes trigger Trig1 to fire. Trig1 updates table T2, which causes trigger Trig2 to fire. Trig2 in turn updates table T1, which causes Trig1 to fire again. Direct recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3, which causes trigger Trig3 to fire. Trig3 updates table T3 again, which causes trigger Trig3 to fire again. When this option is OFF (the default), triggers can't be fired recursively.

Database Recovery Options

The database option RECOVERY (FULL, BULK_LOGGED or SIMPLE) determines how much recovery can be done on a SQL Server database. It also controls how much information is logged and how much of the log is available for backups. I'll cover this option in more detail in Chapter 5.

Two other options also apply to work done when a database is recovered. Setting the TORN_PAGE_DETECTION option to ON or OFF is possible in SQL Server 2005, but that particular option will go away in a future version. The recommended alternative is to set the PAGE_VERIFY option to a value of TORN_PAGE_DETECTION or CHECKSUM. (So TORN_PAGE_DETECTION should now be considered a value, rather the name of an option.)

The PAGE_VERIFY options discover damaged database pages caused by disk I/O path errors, which can cause database corruption problems. The I/O errors themselves are generally caused by power failures or disk failures that occur when a page is being written to disk.

• CHECKSUM When the PAGE_VERIFY option is set to CHECKSUM, SQL Server calculates a checksum over the contents of each page and stores the value in the page header when a page is written to disk. When the page is read from disk, a checksum is recomputed and compared with the value stored in the page header. If the values do not match, error message 824 (indicating a checksum failure) is reported.

• TORN_PAGE_DETECTION When the PAGE_VERIFY option is set to TORN_PAGE_DETECTION, it causes a bit to be flipped for each 512-byte sector in a database page (8 KB) whenever the page is written to disk. It allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages. If a bit is in the wrong state when the page is later read by SQL Server, it means that the page was written incorrectly. (A torn page has been detected.) Although SQL Server database pages are 8 KB, disks perform I/O operations using 512-byte sectors. Therefore, 16 sectors are written per database page. A torn page can occur if the system crashes (for example, because of power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. When the page is read from disk, the torn bits stored in the page header are compared with the actual page sector information. Unmatched values indicate that only part of the page was written to disk. In this situation, error message 824 (indicating a torn page error) is reported. Torn pages are typically detected by database recovery if it is truly an incomplete write of a page. However, other I/O path failures can cause a torn page at any time.

• NONE (No Page Verify Option) You can specify that that neither the CHECKSUM nor the TORN_PAGE_DETCTION value will be generated when a page is written, and these values will not be verified when a page is read.

Both checksum and torn page errors generate error message 824, which is written to both the SQL Server error log and the Windows event log. For any page that generates an 824 error when read, SQL Server will insert a row into the system table suspect_pages in the msdb database. (Books Online has more information on "Understanding and Managing the suspect_pages table.")

SQL Server will retry any read that fails with a checksum, torn page, or other I/O error four times. If the read is successful in any one of those attempts, a message will be written to the error log and the command that triggered the read will continue. If the attempts fail, the command will fail with error message 824.

You can "fix" the error by restoring the data or potentially rebuilding the index if the failure is limited to index pages. If you encounter a checksum failure, you can run DBCC CHECKDB to determine the type of database page or pages affected. You should also determine the root cause of the error and correct the problem as soon as possible to prevent additional or ongoing errors. Finding the root cause requires investigating the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.

In SQL Server 2005, the default is CHECKSUM. In SQL Server 2000, TORN_PAGE_DETECTION is the default, and CHECKSUM is not available. If you upgrade a database from SQL Server 2000, the PAGE_VERIFY value will be NONE or TORN_PAGE_DETECTION. If it is TORN_PAGE_DETECTION, you should consider changing it to CHECKSUM. Although TORN_PAGE_DETECTION uses fewer resources, it provides less protection than CHECKSUM.

Other Database Options

Of the four other main categories of database options, I will cover only one category in detailthe external access options. The snapshot isolation options will be discussed in Chapter 8. The Service Broker options are discussed in Inside SQL Server 2005: TSQL Programming. The database mirroring options are also beyond the scope of this book. Database mirroring is a new SQL Server 2005 technology that provides more options for high availability and is fully supported as of Service Pack 1. (Microsoft did not fully support database mirroring in the initial RTM release of SQL Server 2005, but mirroring can be enabled in that release by using trace flag 1400 as a startup parameter.) All the details, both internal and external, that you might want to know about database mirroring in SQL Server 2005 can be found in the white paper "Database Mirroring in SQL Server 2005" by Ron Talmage and the Microsoft TechNet article "Database Mirroring Best Practices and Performance Considerations" by Sanjay Mishra, which are both included with the companion content for this book.