New databases inherit options from the model database. After you create a database, you can modify these settings at any time by using SQL Server Management Studio, the ALTER DATABASE statement, and other SQL commands. In most SQL Server editions, many standard options can be set to TRUE (ON) or FALSE (OFF) states. Other options accept specific values that specify their configured state, such as GLOBAL or LOCAL.
To set database options in SQL Server Management Studio, complete the following steps:
In SQL Server Management Studio, use Registered Servers view to select a type of server, such as Database Engine. If you need to expand a server group to see the servers listed in the group, click the plus sign (+) next to the name of a group.
In Registered Servers view, select a server by double-clicking its name in the list. This connects you to the server in Object Explorer view.
Click the plus sign (+) next to the Databases folder. Right-click the name of a database, and then choose Properties from the shortcut menu to display the Database Properties dialog box.
In the Database Properties dialog box, select Options from the Select A Page list as shown in Figure 7-4. You can now configure options for the database by selecting or clearing the appropriate check boxes.
Click OK when you are finished selecting options. Your changes take effect immediately, and you do not need to restart the server.
SQL Server Management Studio gives you one easy way to modify the configuration of a database. Another way to modify a database is to use ALTER DATABASE. You can use the ALTER DATABASE command to perform the following tasks:
Set database options. You can use it instead of the sp_dboption stored procedure.
Add new data and log files to a database. All the files must be placed in the same filegroup.
Modify properties of data and log files, such as increasing file size, changing the maximum size, or setting file growth rules.
Add a new filegroup to a database.
Modify the properties of an existing filegroup, such as designating whether the filegroup is read-only or read-write, and which filegroup is the default.
Remove files and filegroups from a database. These elements can be removed only when they do not contain data.
The ALTER DATABASE command is designed to make one database change at a time, and it uses the syntax shown in Example 7-2. The examples in the listing show how you could use ALTER DATABASE to perform important administrative tasks. You can use the Query view in SQL Server Management Studio or SQLCMD. Execute commands with either the Execute Command button or the GO statement, respectively.
Example 7-2. ALTER DATABASE Syntax and Usage
Syntax
ALTER DATABASE database_name
{ <add_or_modify_files>
| <add_or_modify_filegroups>
| <set_database_options>
| MODIFY NAME = new_database_name
| COLLATE collation_name }
[;]
<add_or_modify_files>::=
{ ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name | DEFAULT } ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
| MODIFY FILE <filespec>
}
<filespec>::=
( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | % ] ]
[ , OFFLINE ]
)
<add_or_modify_filegroups>::=
{ | ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILEGROUP filegroup_name
{ <filegroup_updatability_option>
| DEFAULT
| NAME = new_filegroup_name
}
}
<filegroup_updatability_option>::=
{ { READONLY | READWRITE }
| { READ_ONLY | READ_WRITE }
}
<set_database_options>::=
SET {
{ <optionspec> [ ,...n ] [ WITH <termination> ] }
| ALLOW_SNAPSHOT_ISOLATION {ON | OFF }
| READ_COMMITTED_SNAPSHOT {ON | OFF } [ WITH <termination> ]
}
<optionspec>::=
{ <db_state_option>
| <db_user_access_option>
| <db_update_option>
| <external_access_option>
| <cursor_option>
| <auto_option>
| <sql_option>
| <recovery_option>
| <database_mirroring_option>
| <supplemental_logging_option>
| <service_broker_option>
| <date_correlation_optimization_option>
| <parameterization_option>
}
<db_state_option> ::=
{ ONLINE | OFFLINE | EMERGENCY }
<db_user_access_option> ::=
{ SINGLE_USER | RESTRICTED_USER | MULTI_USER }
<db_update_option> ::=
{ READ_ONLY | READ_WRITE }
<external_access_option> ::=
{ DB_CHAINING { ON | OFF }
| TRUSTWORTHY { ON | OFF } }
<cursor_option> ::=
{ CURSOR_CLOSE_ON_COMMIT { ON | OFF }
| CURSOR_DEFAULT { LOCAL | GLOBAL }
}
<auto_option> ::=
{ AUTO_CLOSE { ON | OFF }
| AUTO_CREATE_STATISTICS { ON | OFF }
| AUTO_SHRINK { ON | OFF }
| AUTO_UPDATE_STATISTICS { ON | OFF }
| AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}
<sql_option> ::=
{ 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 }
}
<recovery_option> ::=
{ RECOVERY { FULL | BULK_LOGGED | SIMPLE }
| TORN_PAGE_DETECTION { ON | OFF }
| PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}
<database_mirroring_option> ::=
{ <partner_option> | <witness_option> }
<partner_option> ::=
PARTNER { = 'partner_server'
| FAILOVER
| FORCE_SERVICE_ALLOW_DATA_LOSS
| OFF
| RESUME
| SAFETY { FULL | OFF }
| SUSPEND
| REDO_QUEUE ( integer { KB | MB | GB } | OFF )
| TIMEOUT integer
}
<witness_option> ::=
WITNESS { = 'witness_server'
| OFF }
<supplemental_logging_option> ::=
SUPPLEMENTAL_LOGGING { ON | OFF }
<service_broker_option> ::=
{ ENABLE_BROKER
| DISABLE_BROKER
| NEW_BROKER
| ERROR_BROKER_CONVERSATIONS
}
<date_correlation_optimization_option> ::=
{ DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}
<parameterization_option> ::=
{ PARAMETERIZATION { SIMPLE | FORCED }
}
<termination> ::=
{ ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT
}Usage: Adding a File to a Database
ALTER DATABASE Customer ADD FILE ( NAME = Customerdata2, FILENAME = "c:datacustomerdat2.ndf", SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 5MB )
Usage: Adding a Filegroup
ALTER DATABASE Customer ADD FILEGROUP Secondary
Usage: Adding Files and Placing Them in a Filegroup
ALTER DATABASE Customer ADD FILE ( NAME = Customerdata3, FILENAME = "c:datacustomerdat3.ndf", SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB), ( NAME = Customerdata4, FILENAME = "c:datacustomerdat4.ndf", SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB) TO FILEGROUP Secondary
Usage: Setting the Default Filegroup
ALTER DATABASE Customer MODIFY FILEGROUP Secondary DEFAULT
Usage: Modifying a File
ALTER DATABASE Customer MODIFY FILE (NAME = Customerdata3, SIZE = 20MB)
Usage: Removing a File from a Database
USE Customer DBCC SHRINKFILE (Customerdata3, EMPTYFILE) ALTER DATABASE Customer REMOVE FILE Customerdata3
Usage: Setting the Recovery Model Option
ALTER DATABASE Customer SET RECOVERY FULL GO
Usage: Setting Single User with Rollback of Incomplete Transactions
ALTER DATABASE Customer SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
SQL Server 2005 has several important features that can be managed automatically. You will find the automatic options on the Options page of the Database Properties dialog box (shown in Figure 7-4). These options are shown as TRUE when they are set to ON and FALSE when they are set to OFF. In the following list, Database Properties dialog box options are listed first, with the related ALTER DATABASE keyword following in parentheses. (Note that some options can only be managed in T-SQL as there is no comparable Database Properties dialog box option.) Automatic options include:
Auto Close (auto_close). When this option is set to TRUE, the database closes and resources become available again when the last user connection ends and all database processes are completed. The database reopens automatically when a user connects to the database again. In the SQL Server 2005 Express Edition, this option is set to TRUE by default. All other editions set this option to FALSE by default, which can improve database performance as the overhead of opening and closing databases is eliminated. When FALSE, the database remains open even if no users are currently using it.
Auto Create Statistics (auto_create_statistics). When this option is set to TRUE (the default), statistics are automatically created by SQL Server for columns used in a WHERE clause and as otherwise needed. These statistics are used to determine the best way to evaluate a query, which in turn can improve query performance.
Auto Shrink (auto_shrink). When this option is set to TRUE, data and log files are reduced in size and compacted automatically. When records are deleted or purged, SQL Server automatically reduces the size of data or log files, or both. However, log files are reduced in size only when you back up the transaction log or set the Recovery Model to Simple.
Note
Several caveats apply to Auto Shrink. The Auto Shrink option is applied only when more than 25 percent of a file contains unused space. This causes SQL Server to reduce the file size so that only 25 percent of file space is free or to set the file size to its original size setting, whichever is greater. The process that shrinks the database checks the database size at 30-minute intervals. As with the Autogrowth feature discussed earlier, the database is locked when SQL Server shrinks files, which can reduce query response time. Because of this, it is usually a better choice to run the DBCC SHRINKDATABASE command periodically or to schedule this task on a recurring basis, as explained in the subsection titled "Compressing" later in this chapter.
Auto Update Statistics (auto_update_statistics). When this option is set to TRUE (the default), existing statistics are updated automatically if data in the related tables changes. Otherwise, existing statistics are not updated automatically; you can only update them manually. The UPDATE STATISTICS statement reenables automatic statistical updating unless the NORECOMPUTE clause is specified.
auto_update_statistics_async. When TRUE, queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Otherwise, queries that initiate an automatic update of out-of-date statistics wait for the statistics to be updated before compiling. This option is new for SQL Server 2005 and can only be managed in T-SQL.
To manage the automatic features using SQL Server Management Studio, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select Properties from the shortcut menu.
In the Database Properties dialog box, select Options from the Select A Page list.
Set the individual Automatic options to True or False as necessary. Click OK when you are finished setting options. Your changes take effect immediately without restarting the server.
To manage the automatic features using T-SQL, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select New Query from the shortcut menu.
In the Query view, type ALTER DATABASE <dbname> SET <option> <option_value> GO, where dbname is the name of the database you want to examine, option is the name of the option to set, and option_value is the value for the specified option. The following example shows the commands required to turn on the Auto Shrink option for the Personnel database:
ALTER DATABASE Personnel SET auto_shrink ON GO
Execute the query by clicking Execute or by pressing F5. If the option is set properly, the command should complete successfully.
ANSI compliance can be controlled at the database level using database options. You will find these options listed under the Miscellaneous heading on the Options page of the Database Properties dialog box. The settings for these options are shown as TRUE when they are set to ON and FALSE when they are set to OFF. In the following list, Database Properties dialog box options are listed first, followed by the related ALTER DATABASE keyword in parentheses:
ANSI NULL Default (ansi_null_default). When TRUE, changes the database default to NULL when no value is specified. You can override this setting by explicitly stating NULL or NOT NULL when you create user-defined data types or column definitions.
ANSI Nulls Enabled (ansi_nulls). When this option is set to TRUE, any comparison to a null value evaluates to NULL. Otherwise, comparisons of non-Unicode values evaluate to TRUE only when both values are NULL.
ANSI Padding Enabled (ansi_padding). When this option is set to TRUE, non-null values shorter than the defined column size are padded to fill the length of the column. Values are padded as appropriate for the relevant data type; for example, char columns are padded with trailing blanks, and binary columns are padded with trailing zeroes. When FALSE, trailing blanks are trimmed.
ANSI Warnings Enabled (ansi_warnings). When this option is set to TRUE, SQL Server issues certain warnings that would otherwise not display. For example, if TRUE, divide-by-zero errors are displayed; if FALSE, these errors do not display.
Arithmetic Abort Enabled (arithabort). When this option is set to TRUE, it terminates a query when an overflow or divide-by-zero error occurs. If the error occurs in a transaction, the transaction is rolled back. When the option is set to FALSE, a warning message may display, but queries and transactions continue as if no error occurred.
Concat Null Yields Null (concat_null_yields_null). When this option is set to TRUE, concatenating a string containing NULL with other strings results in NULL. If FALSE, the null value is treated as an empty string.
Numeric Round-Abort (numeric_roundabort). When this option is set to TRUE, an error is generated when a loss of precision occurs in an expression. When it is set to FALSE, losses of precision do not generate error messages, and the result is rounded to the precision of the column or variable storing the result.
Quoted Identifiers Enabled (quoted_identifier). When this option is set to TRUE, identifiers must be delimited by double quotation marks ("…") and literals must be delimited by single quotation marks (’…’). All strings that are delimited by double quotation marks are interpreted as object identifiers and do not have to follow the Transact-SQL rules for identifiers. When FALSE, you only need to use quoted identifiers if names contain spaces.
Recursive Triggers Enabled (recursive_triggers). When this option is set to TRUE, a trigger can execute recursively. Triggers can be executed directly or indirectly. If a trigger is direct, a trigger in Table A1 modifies Table A1, which in turn causes the trigger to fire again. If a trigger is indirect, a trigger in Table A1 could modify data in Table A2, which in turn has a trigger that modifies data in Table A1, and this causes the original trigger to fire again. When FALSE, only indirect triggers are allowed.
To manage the ANSI compliance features using SQL Server Management Studio, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select Properties from the shortcut menu.
In the Database Properties dialog box, select Options from the Select A Page list.
Set the ANSI compliance options to True or False as necessary. Click OK when you have finished setting these options. Your changes take effect immediately without restarting the server.
To manage the ANSI compliance features using T-SQL, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select New Query from the shortcut menu.
In the Query view, type ALTER DATABASE <dbname> SET <option> <option_value> GO, where dbname is the name of the database you want to examine, option is the name of the option to set, and option_value is the value for the specified option. The following example shows the commands required to turn on the numeric_roundabort option for the Personnel database:
ALTER DATABASE Personnel SET numeric_roundabort ON GO
Execute the query by clicking Execute or by pressing F5. If the option is set properly, the command should complete successfully.
Cursors are used with stored procedures, triggers, and in batch scripts to make the contents of a result set available to other statements. You have limited control over cursor behavior using the options listed under the Cursor heading on the Options page of the Database Properties dialog box. These options are shown as TRUE when they are set to ON and FALSE when they are set to OFF. In the following list, Database Properties dialog box options are listed first, with the related ALTER DATABASE keyword following in parentheses:
Cursor Close on Commit Enabled (cursor_close_on_commit). When this option is set to TRUE, open cursors are closed automatically when a transaction is committed or rolled back. This behavior is in compliance with SQL-92, but the option is not set to TRUE by default. As a result, cursors remain open across transaction boundaries, and they close only when the related connection is closed or when the cursor is explicitly closed.
Default Cursor (cursor_default). When this option is set to LOCAL, cursors are created with local scope unless otherwise specified, and as a result, the cursor name is valid only within this scope. When the option is set to GLOBAL, cursors not explicitly set to LOCAL are created with a global scope and can be referenced in any stored procedure, batch, or trigger that the connection executes.
To manage the cursor settings using SQL Server Management Studio, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select Properties from the shortcut menu.
In the Database Properties dialog box, select Options from the Select A Page list.
Set the Cursor options as necessary. Click OK when you have finished setting options. Your changes take effect immediately without restarting the server.
To manage the cursor settings using T-SQL, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select New Query from the shortcut menu.
In the Query view, type ALTER DATABASE <dbname> SET <option> <option_value> GO, where dbname is the name of the database you want to examine, option is the name of the option to set, and option_value is the value for the specified option. The following example shows the commands required to set cursor_default to GLOBAL for the Personnel database:
ALTER DATABASE Personnel SET cursor_default GLOBAL GO
Execute the query by clicking Execute or by pressing F5. If the option is set properly, the command should complete successfully.
As you might expect, managing user access and database state is a complex process. In SQL Management Studio, you can control the general state of the database, including whether the database is read-only or read-write, and who has access to the database.
When a database is set to READ_ONLY, you can read data but not modify it. You use this option to prevent users from changing data and modifying database configuration settings. Several caveats apply when a database is read-only: automatic recovery is skipped at system startup, locking does not take place, and the database will not shrink. The normal mode is READ_WRITE, which allows the database to be read and modified.
When a database is set to SINGLE_USER, only the database owner can access the database. You use this option when you are modifying a database and temporarily want to block access to it. When set to RESTRICTED_USER, only members of the db_owner, dbcreator, or sysadmin roles can use the database. When set to MULTI_USER, all users with the appropriate permissions to connect to the database are permitted to use it.
To manage the database state using SQL Server Management Studio, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select Properties from the shortcut menu.
In the Database Properties dialog box, select Options from the Select A Page list. You can now manage the database state:
To set the database to the READ_WRITE state, set the Database Read-Only option to False.
To set the database to the READ_ONLY state, set the Database Read-Only option to True.
To allow access only to the database owner, set the Restrict Access option to Single.
To allow access to the members of the db_owner, dbcreator, or sysadmin roles, set the Restrict Access option to Restricted.
To allow access to all users with the appropriate permissions to connect to the database, set the Restrict Access option to Multiple.
Click OK when you have finished setting the options. Your changes take effect immediately without restarting the server.
To manage the state settings using T-SQL, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select New Query from the shortcut menu.
In the Query view, type ALTER DATABASE <dbname> SET <keyword> GO, where dbname is the name of the database you want to examine, and keyword is one of these states: READ_ONLY, READ_WRITE, SINGLE_USER, RESTRICTED_USER, or MULTI_USER. The following example shows the commands required to set the Personnel database for multiple-user access:
ALTER DATABASE Personnel SET MULTI_USER GO
Execute the query by clicking Execute or by pressing F5. If the option is set properly, the command should complete successfully.
In SQL Server 2005, you can put an individual database online or offline, or you can set an emergency state that allows you to troubleshoot for database problems. When the option is set to ONLINE, the database is open and available for use. When it is set to OFFLINE, the database is offline, and you can mount or dismount it as necessary. When it is set to EMERGENCY, the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.
Note
In SQL Server 2005, the offline or online state of a database file is maintained independently from the state of the database. For a filegroup to be available, all files in the filegroup must be online. If a filegroup is offline, you cannot query the related data using SQL statements. The query optimizer does not consider the filegroup state when selecting a query plan.
To put a database in the online, offline, or emergency state, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select New Query from the shortcut menu.
In the Query view, type ALTER DATABASE <dbname> SET <keyword> GO, where dbname is the name of the database you want to examine, and keyword is one of these states: ONLINE, OFFLINE, EMERGENCY. The following example shows the commands required to put the Personnel database in the emergency state for troubleshooting:
ALTER DATABASE Personnel SET EMERGENCY GO
Execute the query by clicking Execute or by pressing F5. If the option is set properly, the command should complete successfully.
Ownership chaining is used to determine how multiple objects access each other sequentially. When chaining is allowed, SQL Server compares ownership of a calling object to the owner of the object being called. If both objects have the same owner, the object being called is considered to have the same object permissions as the calling object. In this case, you can achieve a cascade effect if the initial permissions on a view are used when the view needs access to other objects and the owners of these objects are the same.
In some limited circumstances, you might need to configure cross-database ownership chaining between specific databases and across all databases in a single instance. Although this feature is disabled by default, you can enable it using ALTER DATABASE SET DB_CHAINING ON. When DB_CHAINING is TRUE (ON), the database can be the source or target of a cross-database ownership chain. You cannot set DB_CHAINING on master, model, or tempdb, and you must log on as a member of the sysadmin fixed server role to set this option.
A related option is TRUSTWORTHY. When TRUSTWORTHY is TRUE (ON), database modules that use an impersonation context can access resources outside of the database. For example, user-defined functions and stored procedures could access resources outside of the database. By default, the master database has TRUSTWORTHY set to ON. The model and tempdb databases always have TRUSTWORTHY set to OFF, however, and the value cannot be changed for these databases. If you want to permit another database to access outside resources, you must set TRUSTWORTHY to TRUE (ON). You must be logged on as a member of the sysadmin fixed server role to set this option.
To configure chaining or trustworthiness, follow these steps:
In Object Explorer view in SQL Server Management Studio, right-click the database you want to configure, and then select New Query from the shortcut menu.
In the Query view, type ALTER DATABASE <dbname> SET <option> <option_value> GO, where dbname is the name of the database you want to examine, option is the name of the option to set, and option_value is the value for the specified option. The following example shows the commands required to turn on cross-database chaining for the Personnel database:
ALTER DATABASE Personnel SET db_chaining ON GO
Execute the query by clicking Execute or by pressing F5. If the option is set properly, the command should complete successfully.
SQL Server 2005 has several options for managing recovery, logging, and I/O error checking. In SQL Management Studio, you manage recovery settings using the Recovery Model and Page Verify settings on the Options page. In T-SQL, you manage these options using the ALTER DATABASET SET RECOVERY and ALTER DATABASE SET PAGE_VERIFY commands.
Three recovery options are available:
FULL. When recovery is set to FULL, transactions are fully logged, and the database can be recovered to the point of failure or to a specific point in time using the transaction logs.
BULK_LOGGED. When recovery is set to BULK_LOGGED (previously managed using select into/bulk copy), certain SQL commands are not logged in the transaction log. These commands include using SELECT INTO and BULK INSERT with a permanent table, running fast bulk copy, using UPDATETEXT or WRITETEXT without logging, and using a table load. If you set this option and execute any command that bypasses the transaction log, you cannot recover the database from transaction logs, and BACKUP LOG commands are prohibited. Instead, use BACKUP DATABASE to back up the entire database, and then later you can back up from the log (provided that you do not run any more commands that bypass the transaction log).
SIMPLE. When set to SIMPLE (previously managed using trunc. log on chkpt), the transaction log can be automatically truncated. This setting allows the log to be cleared out after transactions have been committed. After the transaction log has been cleared out, you can perform BACKUP/RESTORE only at the database level (and not with the transaction log).
Note
Checkpoints occur at various times. A checkpoint is issued for each database when the SQL Server service shuts down normally. Checkpoints do not occur when the SHUTDOWN WITH NOWAIT statement is used. A checkpoint is executed in a single database when a database is changed with sp_dboption. SQL Server also automatically issues a checkpoint on a database as necessary to ensure that the designated recovery interval can be achieved and when the log becomes 70 percent full.
Note
The transaction log must be large enough to store all active transactions. Otherwise, you cannot roll back transactions. In a deployment environment, you should use this option only when you can rely solely on database backups and do not supplement with transaction log backups. Note also that the tempdb database is always truncated on checkpoint, regardless of the setting of this option.
Disk I/O errors can cause database corruption problems and are usually the result of power failures or disk hardware failures that occur at the time a page is written to disk. There are three page verification options to help identify incomplete I/O transactions caused by disk I/O errors:
CHECKSUM. When PAGE_VERIFY is set to CHECKSUM, checksums are used to find incomplete I/O transactions caused by disk I/O errors. The checksum is computed over the contents of the entire page and stored in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. When there are mismatches, error message 824 is reported to both the SQL Server error log and the Windows Event Viewer. Any I/O errors detected by the operating system are logged with error message 823.
TORN_PAGE_DETECTION. When PAGE_VERIFY is set to TORN_PAGE_DETECTION, a bit is reversed for each 512-byte sector in an 8-KB database page when the page is written to disk. If a bit is in the wrong state when the page is later read, the page was written incorrectly and a torn page is detected. If SQL Server detects a torn page during a user connection, it sends an I/O error message 824 indicating a torn page error and terminates the user connection. If it detects a torn page during recovery, it marks the database as suspect. In either case, you may want to restore the database from backup and apply any backup transaction logs.
NONE. When PAGE_VERIFY is set to NONE (OFF), future data page writes will not contain a checksum or torn page bit, and pages will not be verified at read time, even for previously written pages that contain a checksum or torn page bit.
Note
Previous versions of SQL Server used the TORN_PAGE_DETECTION option to help detect I/O errors. This option is still supported, but its use is usually rejected in favor of PAGE_VERIFY. When TORN_PAGE_DETECTION is set to TRUE (ON), SQL Server automatically detects incomplete I/O operations known as torn pages.
SQL Server 2005 also supports supplemental logging, which adds information to the logs for third-party products. You can enable logging of additional information by setting the SUPPLEMENTAL_LOGGING option to TRUE (ON). Using this option adds a lot of information to the logs, however, and can impact overall performance.
Although SQL Server Management Studio makes it easy to set database options, you will often want to view or change options using SQL commands. To do this, you can use the sp_dboption stored procedure, individual SET commands, or the ALTER DATABASE command. Tasks you can perform with the sp_dboption and SET commands include:
Displaying an options list. To display a list of available options, type EXEC sp_dboption.
Viewing database option settings. To view the current option settings for a database, type EXEC sp_dboption <dbname>, where dbname is the name of the database you want to examine, such as EXEC sp_dboption Subs.
Enabling database options. To turn on a database option, type ALTER DATABASE <dbname> SET <option>, where dbname is the name of the database you want to examine and option is the name of the option flag to set to the TRUE (ON) state.
Setting specific database option values. To set a specific database option value, type ALTER DATABASE <dbname> SET <option> <option_value>, where dbname is the name of the database you want to examine, option is the name of the option to set, and option_value is the value for the specified option.
Overriding database options. Use SET options for individual sessions or database drivers to override default settings. You can also check options using properties of the Databaseproperty function. See the section titled "Working with SET Options" in Chapter 4, for more information.