ALTER DATABASE (Transact-SQL)

  • Article

Modifies a database, or the files and filegroups associated with the database. Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Database snapshots cannot be modified. To modify database options associated with replication, use sp_replicationdboption.

Topic link iconTransact-SQL Syntax Conventions

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 } ]
  | 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 | GB | TB| % ] ] 
    [ , 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> ] }
}

<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>
  | <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 
            | TIMEOUT integer
            }
    <witness_option> ::=
    WITNESS { = 'witness_server' 
            | 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 }
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION {ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<termination> ::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

Arguments

  • database_name
    Is the name of the database to be modified.
  • MODIFY NAME **=**new_database_name
    Renames the database with the name specified as new_database_name.

  • COLLATE collation_name
    Specifies the collation for the database. collation_name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the collation of the instance of SQL Server.

    For more information about the Windows and SQL collation names, see COLLATE (Transact-SQL).

<add_or_modify_files>::=

Specifies the file to be added, removed, or modified.

  • ADD FILE
    Adds a file to the database.

    • TO FILEGROUP { filegroup_name }
      Specifies the filegroup to which to add the specified file. To display the current filegroups and which filegroup is the current default, use the sys.filegroups catalog view.
  • ADD LOG FILE
    Adds a log file be added to the specified database.

  • REMOVE FILE logical_file_name
    Removes the logical file description from an instance of SQL Server and deletes the physical file. The file cannot be removed unless it is empty.

    • logical_file_name
      Is the logical name used in SQL Server when referencing the file.

  • MODIFY FILE
    Specifies the file that should be modified. Only one <filespec> property can be changed at a time. NAME must always be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.

    To modify the logical name of a data file or log file, specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. For example:

    MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

    To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:

    MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

    When you move a full-text catalog, specify only the new path in the FILENAME clause. Do not specify the operating-system file name.

    For more information, see Moving Database Files.

<filespec>::=

Controls the file properties.

  • NAME logical_file_name
    Specifies the logical name of the file.

    • logical_file_name
      Is the logical name used in an instance of SQL Server when referencing the file.

  • NEWNAME new_logical_file_name
    Specifies a new logical name for the file.

    • new_logical_file_name
      Is the name to replace the existing logical file name. The name must be unique within the database and comply with the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier. For more information, see Using Identifiers As Object Names.

  • FILENAME 'os_file_name'
    Specifies the operating system (physical) file name.

    • ' os_file_name '
      Is the path and file name that is used by the operating system when you create the file. The file must reside on the server on which SQL Server is installed. The specified path must exist before executing the ALTER DATABASE statement.

      SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set when a UNC path is specified for the file.

      Data files should not be put on compressed file systems unless the files are read-only secondary files, or if the database is read-only. Log files should never be put on compressed file systems. For more information, see Read-Only Filegroups and Compression.

      If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be put on each raw partition.

  • SIZE size
    Specifies the file size.

    • size
      Is the size of the file.

      When specified with ADD FILE, size is the initial size for the file. When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.

      When size is not supplied for the primary file, the SQL Server 2005 Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB.

      The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. For example, specify 1536 KB instead of 1.5 MB (1.5 x 1024 = 1536).

  • MAXSIZE { max_size| UNLIMITED }
    Specifies the maximum file size to which the file can grow.

    • max_size
      Is the maximum file size. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. If max_size is not specified, the file size will increase until the disk is full.
    • UNLIMITED
      Specifies that the file grows until the disk is full. In SQL Server 2005, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.

  • FILEGROWTH growth_increment
    Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.

    • growth_increment
      Is the amount of space added to the file every time new space is required.

      The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.

      A value of 0 indicates that automatic growth is set to off and no additional space is allowed.

      If FILEGROWTH is not specified, the default value is 1 MB for data files and 10% for log files, and the minimum value is 64 KB.

      Note

      In SQL Server 2005, the default growth increment for data files has changed from 10% to 1 MB. The log file default of 10% remains unchanged.

  • OFFLINE
    Sets the file offline and makes all objects in the filegroup inaccessible.

    Warning

    Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Add, modify, or remove a filegroup from the database.

  • ADD FILEGROUP filegroup_name
    Adds a filegroup to the database.
  • REMOVE FILEGROUP filegroup_name
    Removes a filegroup from the database. The filegroup cannot be removed unless it is empty. Remove all files from the filegroup first. For more information, see "REMOVE FILE logical_file_name," earlier in this topic.

  • MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
    Modifies the filegroup by setting the status to READ_ONLY or READ_WRITE, making the filegroup the default filegroup for the database, or changing the filegroup name.

    • <filegroup_updatability_option>
      Sets the read-only or read/write property to the filegroup.
    • DEFAULT
      Changes the default database filegroup to filegroup_name. Only one filegroup in the database can be the default filegroup. For more information, see Understanding Files and Filegroups.
    • NAME = new_filegroup_name
      Changes the filegroup name to the new_filegroup_name.
<filegroup_updatability_option>::=

Sets the read-only or read/write property to the filegroup.

  • READ_ONLY | READONLY
    Specifies the filegroup is read-only. Updates to objects in it are not allowed. The primary filegroup cannot be made read-only. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.

    Because a read-only database does not allow data modifications:

    • Automatic recovery is skipped at system startup.
    • Shrinking the database is not possible.
    • No locking occurs in read-only databases. This can cause faster query performance.

    Note

    The keyword READONLY will be removed in a future version of Microsoft SQL Server. Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. Use READ_ONLY instead.

  • READ_WRITE | READWRITE
    Specifies the group is READ_WRITE. Updates are enabled for the objects in the filegroup. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.

    Note

    The keyword READWRITE will be removed in a future version of Microsoft SQL Server. Avoid using READWRITE in new development work, and plan to modify applications that currently use READWRITE. Use READ_WRITE instead.

The status of these options can be determined by examining the is_read_only column in the sys.databases catalog view or the Updateability property of the DATABASEPROPERTYEX function.

<db_state_option>::=

Controls the state of the database.

  • OFFLINE
    The database is closed, shut down cleanly, and marked offline. The database cannot be modified while it is offline.
  • ONLINE
    The database is open and available for use.
  • EMERGENCY
    The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect due to a corrupted log file can be set to the EMERGENCY state. This could enable the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

The status of this option can be determined by examining the state and state_desc columns in the sys.databases catalog view or the Status property of the DATABASEPROPERTYEX function. For more information, see Database States.

A database marked as RESTORING cannot be set to OFFLINE, ONLINE, or EMERGENCY. A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file. For more information, see Responding to SQL Server Restore Errors Caused by Damaged Backups.

<db_user_access_option> ::=

Controls user access to the database.

  • SINGLE_USER
    Specifies that only one user at a time can access the database. If SINGLE_USER is specified and there are other users connected to the database the ALTER DATABASE statement will be blocked until all users disconnect from the specified database. To override this behavior, see the WITH <termination> clause.

    The database remains in SINGLE_USER mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

    Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:

    1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.
    2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.
    3. If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.
  • RESTRICTED_USER
    RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.
  • MULTI_USER
    All users that have the appropriate permissions to connect to the database are allowed.

The status of this option can be determined by examining the user_access column in the sys.databases catalog view or the UserAccess property of the DATABASEPROPERTYEX function.

<db_update_option>::=

Controls whether updates are allowed on the database.

  • READ_ONLY
    Users can read data from the database but not modify it.
  • READ_WRITE
    The database is available for read and write operations.

To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.

<external_access_option>::=

Controls whether the database can be accessed by external resources, such as objects from another database.

  • DB_CHAINING { ON | OFF }

    • ON
      Database can be the source or target of a cross-database ownership chain.
    • OFF
      Database cannot participate in cross-database ownership chaining.

    Important

    The instance of SQL Server will recognize this setting when the cross db ownership chaining server option is 0 (OFF). When cross db ownership chaining is 1 (ON), all user databases can participate in cross-database ownership chains, regardless of the value of this option. This option is set by using sp_configure.

    To set this option, requires membership in the sysadmin fixed server role. The DB_CHAINING option cannot be set on these system databases: master, model, and tempdb.

    The status of this option can be determined by examining the is_db_chaining_on column in the sys.databases catalog view.

    For more information, see Ownership Chains.

  • TRUSTWORTHY { ON | OFF }

    • ON
      Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.
    • OFF
      Database modules in an impersonation context cannot access resources outside the database.

    TRUSTWORTHY is set to OFF whenever the database is attached.

    By default, all system databases except the msdb database have TRUSTWORTHY set to OFF. The value cannot be changed for the model and tempdb databases. We recommend that you never set the TRUSTWORTHY option to ON for the master database.

    To set this option, requires membership in the sysadmin fixed server role.

    The status of this option can be determined by examining the is_trustworthy_on column in the sys.databases catalog view.

<cursor_option>::=

Controls cursor options.

  • CURSOR_CLOSE_ON_COMMIT { ON | OFF }

    • ON
      Any cursors open when a transaction is committed or rolled back are closed.
    • OFF
      Cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

    Connection-level settings that are set by using the SET statement override the default database setting for CURSOR_CLOSE_ON_COMMIT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting CURSOR_CLOSE_ON_COMMIT to OFF for the session when connecting to an instance of SQL Server. For more information, see SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

    The status of this option can be determined by examining the is_cursor_close_on_commit_on column in the sys.databases catalog view or the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTYEX function.

  • CURSOR_DEFAULT { LOCAL | GLOBAL }
    Controls whether cursor scope uses LOCAL or GLOBAL.

    • LOCAL
      When LOCAL is specified and a cursor is not defined as GLOBAL when created, the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor 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 a stored procedure OUTPUT parameter. The cursor is implicitly deallocated when the batch, stored procedure, or trigger ends, unless it was passed back in an OUTPUT parameter. If the cursor is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable that references it is deallocated or goes out of scope.
    • GLOBAL
      When GLOBAL is specified, and a cursor is not defined as LOCAL when 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.

    The cursor is implicitly deallocated only at disconnect. For more information, see DECLARE CURSOR (Transact-SQL).

    The status of this option can be determined by examining the is_local_cursor_default column in the sys.databases catalog view or the IsLocalCursorsDefault property of the DATABASEPROPERTYEX function.

<auto_option>::=

Controls automatic options.

  • AUTO_CLOSE { ON | OFF }

    • ON
      The database is shut down cleanly and its resources are freed after the last user exits.

      The database automatically reopens when a user tries to use the database again. For example, by issuing a USE database_name statement. If the database is shut down cleanly while AUTO_CLOSE is set to ON, the database is not reopened until a user tries to use the database the next time the Database Engine is restarted.

    • OFF
      The database remains open after the last user exits.

    The AUTO_CLOSE option is useful for desktop databases because it allows for database files to be managed as regular files. They can be moved, copied to make backups, or even e-mailed to other users.

    Note

    In earlier versions of SQL Server, AUTO_CLOSE is a synchronous process that can degrade performance when the database is accessed by an application that repeatedly makes and breaks connections to the Database Engine. In SQL Server 2005, the AUTO_CLOSE process is asynchronous; repeatedly opening and closing the database no longer reduces performance.

    The status of this option can be determined by examining the is_auto_close_on column in the sys.databases catalog view or the IsAutoClose property of the DATABASEPROPERTYEX function.

    Note

    When AUTO_CLOSE is ON, some columns in the sys.databases catalog view and DATABASEPROPERTYEX function will return NULL because the database is unavailable to retrieve the data. To resolve this, execute a USE statement to open the database.

    Note

    Database mirroring requires AUTO_CLOSE OFF.

    When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.

  • AUTO_CREATE_STATISTICS { ON | OFF }

    • ON
      Any missing statistics required by a query for optimization are automatically built during query optimization.

      Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query. If the statistics are not used, the Database Engine automatically deletes them. When set to OFF, statistics are not automatically created; instead, statistics can be manually created. For more information, see Index Statistics.

    • OFF
      Statistics must be manually created.

    The status of this option can be determined by examining the is_auto_update_stats_on column in the sys.databases catalog view or the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.

    Note

    The query optimizer treats all internal system tables as if the AUTO_CREATE_STATISTICS setting is ON, regardless of the actual setting. These tables include system base tables, XML indexes, full-text indexes, service broker queue tables, and query notification tables.

  • AUTO_SHRINK { ON | OFF }

    • ON
      The database files are candidates for periodic shrinking.

      Both data file and log files can be automatically shrunk. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or if the log is backed up. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space.

      The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is larger.

      You cannot shrink a read-only database.

    • OFF
      The database files are not automatically shrunk during periodic checks for unused space.

    The status of this option can be determined by examining the is_auto_shrink_on column in the sys.databases catalog view or the IsAutoShrink property of the DATABASEPROPERTYEX function. .

  • AUTO_UPDATE_STATISTICS { ON | OFF }

    • ON
      Any out-of-date statistics required by a query for optimization are automatically updated during query optimization.
    • OFF
      Statistics must be manually updated.

    Note

    The UPDATE STATISTICS statement reenables automatic statistical updating on the target table or view unless the NORECOMPUTE clause is specified.

    Note

    The query optimizer treats all internal system tables as if the AUTO_UPDATE_STATISTICS setting is ON, regardless of the actual setting. These tables include system base tables, xml indexes, full-text indexes, service broker queue tables, and query notification tables.

    For more information, see Index Statistics.

  • AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

    • ON
      Queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available.
    • OFF
      Queries that initiate an automatic update of out-of-date statistics wait until the updated statistics can be used in the query optimization plan.

    Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

    For more information, see Index Statistics.

<sql_option>::=

Controls the ANSI compliance options at the database level.

  • ANSI_NULL_DEFAULT { ON | OFF }
    Determines the default value, NULL or NOT NULL, of a column, alias data type, or CLR user-defined type for which the nullability is not explicitly defined in CREATE TABLE or ALTER TABLE statements. Columns that are defined with constraints follow constraint rules regardless of this setting.

    • ON
      The default value is NULL.
    • OFF
      The default value is NOT NULL.

    Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session when connecting to an instance of SQL Server. For more information, see SET ANSI_NULL_DFLT_ON (Transact-SQL).

    For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.

    The status of this option can be determined by examining the is_ansi_null_default_on column in the sys.databases catalog view or the IsAnsiNullDefault property of the DATABASEPROPERTYEX function.

  • ANSI_NULLS { ON | OFF }

    • ON
      All comparisons to a null value evaluate to UNKNOWN.
    • OFF
      Comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

    Connection-level settings that are set by using the SET statement override the default database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to an instance of SQL Server. For more information, see SET ANSI_NULLS (Transact-SQL).

    SET ANSI_NULLS also must be set to ON when you create or make changes to indexes on computed columns or indexed views.

    The status of this option can be determined by examining the is_ansi_nulls_on column in the sys.databases catalog view or the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

  • ANSI_PADDING { ON | OFF }

    • ON
      Strings are padded to the same length before conversion or inserting to a varchar or nvarchar data type.

      Trailing blanks in character values inserted into varchar or nvarchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column.

    • OFF
      Trailing blanks for varchar or nvarchar and zeros for varbinary are trimmed.

    When OFF is specified, this setting affects only the definition of new columns.

    char(n) and binary(n) columns that allow for nulls are padded to the length of the column when ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when ANSI_PADDING is OFF. char(n) and binary(n) columns that do not allow nulls are always padded to the length of the column.

    Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_PADDING. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_PADDING to ON for the session when connecting to an instance of SQL Server. For more information, see SET ANSI_PADDING (Transact-SQL).

    Important

    We recommend that ANSI_PADDING always be set to ON. ANSI_PADDING must be ON when you create or manipulate indexes on computed columns or indexed views.

    The status of this option can be determined by examining the is_ansi_padding_on column in the sys.databases catalog view or the IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.

  • ANSI_WARNINGS { ON | OFF }

    • ON
      Errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions.
    • OFF
      No warnings are raised and null values are returned when conditions such as divide-by-zero occur.

    SET ANSI_WARNINGS must be set to ON when you create or make changes to indexes on computed columns or indexed views.

    Connection-level settings that are set by using the SET statement override the default database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session when connecting to an instance of SQL Server. For more information, see SET ANSI_WARNINGS (Transact-SQL).

    The status of this option can be determined by examining the is_ansi_warnings_on column in the sys.databases catalog view or the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.

  • ARITHABORT { ON | OFF }

    • ON
      A query is ended when an overflow or divide-by-zero error occurs during query execution.
    • OFF
      A warning message is displayed when one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred.

    SET ARITHABORT must be set to ON when you create or make changes to indexes on computed columns or indexed views.

    The status of this option can be determined by examining the is_arithabort_on column in the sys.databases catalog view or the IsArithmeticAbortEnabled property of the DATABASEPROPERTYEX function.

  • CONCAT_NULL_YIELDS_NULL { ON | OFF }

    • ON
      The result of a concatenation operation is NULL when either operand is NULL. For example, concatenating the character string "This is" and NULL causes the value NULL, instead of the value "This is".
    • OFF
      The null value is treated as an empty character string.

    CONCAT_NULL_YIELDS_NULL must be set to ON when you create or make changes to indexes on computed columns or indexed views.

    Connection-level settings that are set by using the SET statement override the default database setting for CONCAT_NULL_YIELDS_NULL. By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to an instance of SQL Server. For more information, see SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

    The status of this option can be determined by examining the is_concat_null_yields_null_on column in the sys.databases catalog view or the IsNullConcat property of the DATABASEPROPERTYEX function.

  • QUOTED_IDENTIFIER { ON | OFF }

    • ON
      Double quotation marks can be used to enclose delimited identifiers.

      All strings delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be keywords and can include characters not generally allowed in Transact-SQL identifiers. If a single quotation mark (') is part of the literal string, it can be represented by double quotation marks (").

    • OFF
      Identifiers cannot be in quotation marks and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

    SQL Server also allows for identifiers to be delimited by square brackets ([ ]). Bracketed identifiers can always be used, regardless of the setting of QUOTED_IDENTIFIER. For more information, see Delimited Identifiers (Database Engine).

    When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata of the table, even if the option is set to OFF when the table is created.

    Connection-level settings that are set by using the SET statement override the default database setting for QUOTED_IDENTIFIER. By default, ODBC and OLE DB clients issue a connection-level SET statement setting QUOTED_IDENTIFIER to ON when connecting to an instance of SQL Server. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

    The status of this option can be determined by examining the is_quoted_identifier_on column in the sys.databases catalog view or the IsQuotedIdentifiersEnabled property of the DATABASEPROPERTYEX function.

  • NUMERIC_ROUNDABORT { ON | OFF }

    • ON
      An error is generated when loss of precision occurs in an expression.
    • OFF
      Losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

    NUMERIC_ROUNDABORT must be set to OFF when you create or make changes to indexes on computed columns or indexed views.

    The status of this option can be determined by examining the is_numeric_roundabort_on column in the sys.databases catalog view or the IsNumericRoundAbortEnabled property of the DATABASEPROPERTYEX function.

  • RECURSIVE_TRIGGERS { ON | OFF }

    • ON
      Recursive firing of AFTER triggers is allowed.
    • OFF
      Only direct recursive firing of AFTER triggers is not allowed. To also disable indirect recursion of AFTER triggers, set the nested triggers server option to 0 by using sp_configure.

    Note

    Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. To disable indirect recursion, you must also set the nested triggers server option to 0.

    The status of this option can be determined by examining the is_recursive_triggers_on column in the sys.databases catalog view or the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.

<recovery_option> ::=

Controls database recovery options and disk I/O error checking.

  • FULL
    Provides full recovery after media failure by using transaction log backups. If a data file is damaged, media recovery can restore all committed transactions. For more information, see Backup Under the Full Recovery Model.
  • BULK_LOGGED
    Provides recovery after media failure by combining the best performance and least amount of log-space use for certain large-scale or bulk operations. For information about what operations are bulk logged, see Minimally Logged Operations. Under the BULK_LOGGED recovery model, logging for these operations is minimal. For more information, see Backup Under the Bulk-Logged Recovery Model.

  • SIMPLE
    A simple backup strategy that uses minimal log space is provided. Log space can be automatically reused when it is no longer required for server failure recovery. For more information, see Backup Under the Simple Recovery Model.

    Important

    The simple recovery model is easier to manage than the other two models but at the expense of greater data loss exposure if a data file is damaged. All changes since the most recent database or differential database backup are lost and must be manually reentered.

The default recovery model is determined by the recovery model of the model database. For more information about selecting the appropriate recovery model, see Choosing the Recovery Model for a Database.

The status of this option can be determined by examining the recovery_model and recovery_model_desc columns in the sys.databases catalog view or the Recovery property of the DATABASEPROPERTYEX function.

  • TORN_PAGE_DETECTION { ON | OFF }

    • ON
      Incomplete pages can be detected by the Database Engine.
    • OFF
      Incomplete pages cannot be detected by the Database Engine.

    Important

    The syntax structure TORN_PAGE_DETECTION ON | OFF will be removed in a future version of Microsoft SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the syntax structure. Use the PAGE_VERIFY option instead.

  • PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
    Discovers damaged database pages caused by disk I/O path errors. Disk I/O path errors can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk.

    • CHECKSUM
      Calculates a checksum over the contents of the whole page and stores the value 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. If the values do not match, error message 824 (indicating a checksum failure) is reported to both the SQL Server error log and the Windows event log. A checksum failure indicates an I/O path problem. To determine the root cause requires investigation of the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.
    • TORN_PAGE_DETECTION
      Saves a specific bit for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to 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 to both the SQL Server error log and the Windows event log. 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
      Database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.

    Consider the following important points when you use the PAGE_VERIFY option:

    • In SQL Server 2005, the default is CHECKSUM. In SQL Server 2000, TORN_PAGE_DETECTION is the default.
    • When a user or system database is upgraded to SQL Server 2005, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. We recommend that you use CHECKSUM.
    • TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection.
    • PAGE_VERIFY can be set without taking the database offline, locking the database, or otherwise impeding concurrency on that database.
    • CHECKSUM is mutually exclusive to TORN_PAGE_DETECTION. Both options cannot be enabled at the same time.

    When a torn page or checksum failure is detected, you can recover by restoring the data or potentially rebuilding the index if the failure is limited only to index pages. If you encounter a checksum failure, to determine the type of database page or pages affected, run DBCC CHECKDB. For more information about restore options, see RESTORE Arguments (Transact-SQL). Although restoring the data will resolve the data corruption problem, the root cause, for example, disk hardware failure, should be diagnosed and corrected as soon as possible to prevent continuing errors.

    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 the retry attempts, a message will be written to the error log and the command that triggered the read will continue. If the retry attempts fail, the command will fail with error message 824.

    For more information about checksum, torn page, read-retry, error messages 823 and 824, and other SQL Server I/O auditing features, see this Microsoft Web site.

    The current setting of this option can be determined by examining the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.

<database_mirroring_option>::=

Controls database mirroring for a database. Values specified with the database mirroring options apply to both copies of the database and to the database mirroring session as a whole. Only one <database_mirroring_option> is permitted per ALTER DATABASE statement: { SET PARTNER <partner_option> | SET WITNESS <witness_option>}.

Important

A SET PARTNER or SET WITNESS command can complete successfully when entered, but fail later.

Note

We recommend that you configure database mirroring during off-peak hours because configuration can affect performance.

For information about database mirroring, see Database Mirroring.

  • PARTNER <partner_option>
    Controls the database properties that define the failover partners of a database mirroring session and their behavior. Some SET PARTNER options can be set on either partner; others are restricted to the principal server or to the mirror server. For more information, see the individual PARTNER options that follow. A SET PARTNER clause affects both copies of the database, regardless of the partner on which it is specified.

    To execute a SET PARTNER statement, the STATE of the endpoints of both partners must be set to STARTED. Note, also, that the ROLE of the database mirroring endpoint of each partner server instance must be set to either PARTNER or ALL. For information about how to specify an endpoint, see How to: Create a Mirroring Endpoint for Windows Authentication (Transact-SQL). To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQL statement:

    SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

    <partner_option> ::=

    Note

    Only one <partner_option> is permitted per SET PARTNER clause.

    • 'partner_server'
      Specifies the server network address of an instance of SQL Server to act as a failover partner in a new database mirroring session. Each session requires two partners: one starts as the principal server, and the other starts as the mirror server. We recommend that these partners reside on different computers.

      This option is specified one time per session on each partner. Initiating a database mirroring session requires two ALTER DATABASE database SET PARTNER ='partner_server' statements. Their order is significant. First, connect to the mirror server, and specify the principal server instance as partner_server (SET PARTNER ='principal_server'). Second, connect to the principal server, and specify the mirror server instance as partner_server (SET PARTNER ='mirror_server'); this starts a database mirroring session between these two partners. For more information, see Setting Up Database Mirroring.

      The value of partner_server is a server network address. This has the following syntax:

      TCP**://<system-address>:**<port>

      where

      • <system-address> is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.
      • <port> is a port number that is associated with the mirroring endpoint of the partner server instance.

      For more information, see Specifying a Server Network Address (Database Mirroring).

      The following example illustrates the SET PARTNER ='partner_server' clause:

      SET PARTNER = 'TCP://MYSERVER.mydomain.Adventure-Works.com:7777'

      Important

      If a session is set up by using the ALTER DATABASE statement instead of SQL Server Management Studio, the session is set to full transaction safety by default (SAFETY is set to FULL) and runs in high-safety mode without automatic failover. To allow automatic failover, configure a witness; to run in high-performance mode, turn off transaction safety (SAFETY OFF).

    • FAILOVER
      Manually fails over the principal server to the mirror server. You can specify FAILOVER only on the principal server. This option is valid only when the SAFETY setting is FULL (the default).

      The FAILOVER option requires master as the database context.

      For more information, see Manual Failover.

    • FORCE_SERVICE_ALLOW_DATA_LOSS
      Forces database service to the mirror database after the principal server fails with the database in an unsynchronized state or in a synchronized state when automatic failover does not occur.

      We strongly recommend that you force service only if the principal server is no longer running. Otherwise, some clients might continue to access the original principal database instead of the new principal database.

      FORCE_SERVICE_ALLOW_DATA_LOSS is available only on the mirror server and only under all the following conditions:

      • The principal server is down.
      • WITNESS is set to OFF or the witness is connected to the mirror server.

      Force service only if you are willing to risk losing some data in order to restore service to the database immediately. For information about alternatives to forcing service, see Asynchronous Database Mirroring (High-Performance Mode).

      Forcing service suspends the session, temporarily preserving all the data in the original principal database. Once the original principal is in service and able to communicate with the new principal server, the database administrator can resume service. When the session resumes, any unsent log records and the corresponding updates are lost.

      For more information about the risks of forcing service, see Forced Service (with Possible Data Loss).

    • OFF
      Removes a database mirroring session and removes mirroring from the database. You can specify OFF on either partner. For information, see about the impact of removing mirroring, see Removing Database Mirroring.
    • RESUME
      Resumes a suspended database mirroring session. You can specify RESUME only on the principal server.

    • SAFETY { FULL | OFF }
      Sets the level of transaction safety. You can specify SAFETY only on the principal server.

      The default is FULL. With full safety, the database mirroring session funs synchronously (in high-safety mode). If SAFETY is set to OFF, the database mirroring session runs asynchronously (in high-performance mode).

      The behavior of high-safety mode depends partly on the witness, as follows:

      • When safety is set to FULL and a witness is set for the session, the session runs in high-safety mode with automatic failover. When the principal server is lost, the session automatically fails over if the database is synchronized and the mirror server instance and witness are still connected to each other (that is, they have quorum). For more information, see Quorum: How a Witness Affects Database Availability.
        If a witness is set for the session but is currently disconnected, the loss of the mirror server causes the principal server to go down.
      • When safety is set to FULL and the witness is set to OFF, the session runs in high-safety mode without automatic failover. If the mirror server instance goes down, the principal server instance is unaffected. If the principal server instance goes down, you can force service (with possible data loss) to the mirror server instance.

      If SAFETY is set to OFF, the session runs in high-performance mode, and automatic failover and manual failover are not supported. However, problems on the mirror do not affect the principal, and if the principal server instance goes down, you can, if necessary, force service (with possible data loss) to the mirror server instance—if WITNESS is set to OFF or the witness is currently connected to the mirror. For more information on forcing service, see "FORCE_SERVICE_ALLOW_DATA_LOSS" earlier in this section.

      Important

      High-performance mode is not intended to use a witness. However, whenever you set SAFETY to OFF, we strongly recommend that you ensure that WITNESS is set to OFF.

      For more information, see Transact-SQL Settings and Database Mirroring Operating Modes.

    • SUSPEND
      Suspends a database mirroring session.

      You can specify SUSPEND on either partner.

    • TIMEOUT integer
      Specifies the time-out period in seconds. The time-out period is the maximum time that a server instance waits to receive a PING message from another instance in the mirroring session before considering that other instance to be disconnected.

      You can specify the TIMEOUT option only on the principal server. If you do not specify this option, by default, the time period is 10 seconds. If you specify 5 or greater, the time-out period is set to the specified number of seconds. If you specify a time-out value of 0 to 4 seconds, the time-out period is automatically set to 5 seconds.

      Important

      We recommend that you keep the time-out period at 10 seconds or greater. Setting the value to less than 10 seconds creates the possibility of a heavily loaded system missing PINGs and declaring a false failure.

      For more information, see Possible Failures During Database Mirroring.

  • WITNESS <witness_option>
    Controls the database properties that define a database mirroring witness. A SET WITNESS clause affects both copies of the database, but you can specify SET WITNESS only on the principal server. If a witness is set for a session, quorum is required to serve the database, regardless of the SAFETY setting; for more information, see Quorum: How a Witness Affects Database Availability.

    We recommend that the witness and failover partners reside on separate computers. For information about the witness, see Database Mirroring Witness. For information about automatic failover, see Automatic Failover.

    To execute a SET WITNESS statement, the STATE of the endpoints of both the principal and witness server instances must be set to STARTED. Note, also, that the ROLE of the database mirroring endpoint of a witness server instance must be set to either WITNESS or ALL. For information about specifying an endpoint, see Database Mirroring Endpoint.

    To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQL statement:

    SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

    Note

    Database properties cannot be set on the witness.

    <witness_option> ::=

    Note

    Only one <witness_option> is permitted per SET WITNESS clause.

    • 'witness_server'
      Specifies an instance of the Database Engine to act as the witness server for a database mirroring session. You can specify SET WITNESS statements only on the principal server.

      In a SET WITNESS ='witness_server' statement, the syntax of witness_server is the same as the syntax of partner_server.

    • OFF
      Removes the witness from a database mirroring session. Setting the witness to OFF disables automatic failover. If the database is set to FULL SAFETY and the witness is set to OFF, a failure on the mirror server causes the principal server to make the database unavailable.
<service_broker_option>::=

Controls service broker options

  • ENABLE_BROKER
    Specifies that Service Broker is enabled for the specified database. The is_broker_enabled flag is set to true in the sys.databases catalog view and message delivery is started.

    Note

    Enabling SQL Server Service Broker in any database requires a database lock. To enable Service Broker in the msdb database, first stop SQL Server Agent, so Service Broker can obtain the necessary lock.

  • DISABLE_BROKER
    Specifies that Service Broker is disabled for the specified database. The is_broker_enabled flag is set to false in the sys.databases catalog view and message delivery is stopped.
  • NEW_BROKER
    Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages.
  • ERROR_BROKER_CONVERSATIONS
    Specifies that conversations in the database should receive an error message when the database is attached. This enables your applications to perform regular clean up for existing conversations.
<date_correlation_optimization_option> ::=

Controls the date_correlation_optimization option.

  • DATE_CORRELATION_OPTIMIZATION { ON | OFF }

    • OFF
      Correlation statistics are not maintained.

    To set DATE_CORRELATION_OPTIMIZATION to ON, there must be no active connections to the database except for the connection that is executing the ALTER DATABASE statement. Afterwards, multiple connections are supported.

    The current setting of this option can be determined by examining the is_date_correlation_on column in the sys.databases catalog view.

<parameterization_option> ::=

Controls the parameterization option.

  • PARAMETERIZATION { SIMPLE | FORCED }

    • SIMPLE
      Queries are parameterized based on the default behavior of the database. For more information, see Simple Parameterization.

    The current setting of this option can be determined by examining the is_parameterization_forced column in the sys.databases catalog view.

<snapshot_option>::=

Determines the transaction isolation level.

  • ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

    • ON
      Transactions can specify the SNAPSHOT transaction isolation level. When a transaction runs at the SNAPSHOT isolation level, all statements see a snapshot of data as it exists at the start of the transaction. If a transaction running at the SNAPSHOT isolation level accesses data in multiple databases, either ALLOW_SNAPSHOT_ISOLATION must be set to ON in all the databases, or each statement in the transaction must use locking hints on any reference in a FROM clause to a table in a database where ALLOW_SNAPSHOT_ISOLATION is OFF.
    • OFF
      Transactions cannot specify the SNAPSHOT transaction isolation level.

    When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE does not return control to the caller until all existing transactions in the database are committed. If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. If the ALTER DATABASE statement does not return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. The sys.databases catalog view indicates the state of snapshot-isolation transactions in the database. If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.

    You cannot change the state of ALLOW_SNAPSHOT_ISOLATION if the database is OFFLINE.

    If you set ALLOW_SNAPSHOT_ISOLATION in a READ_ONLY database, the setting will be retained if the database is later set to READ_WRITE.

    You can change the ALLOW_SNAPSHOT_ISOLATION settings for the master, model, msdb, and tempdb databases. If you change the setting for tempdb, the setting is retained every time the instance of the Database Engine is stopped and restarted. If you change the setting for model, that setting becomes the default for any new databases that are created, except for tempdb.

    The option is ON, by default, for the master and msdb databases.

    The current setting of this option can be determined by examining the snapshot_isolation_state column in the sys.databases catalog view.

  • READ_COMMITTED_SNAPSHOT { ON | OFF }

    • ON
      Transactions specifying the read committed isolation level use row versioning instead of locking. When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.
    • OFF
      Transactions specifying the READ_COMMITTED isolation level use locking.

    In order to set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection executing the ALTER DATABASE command. However, the database does not have to be in single-user mode. You cannot change the state of this option when the database is OFFLINE.

    If you set READ_COMMITTED_SNAPSHOT in a READ_ONLY database, the setting will be retained when the database is later set to READ_WRITE.

    READ_COMMITTED_SNAPSHOT cannot be turned ON for the master, tempdb, or msdb system databases. If you change the setting for model, that setting becomes the default for any new databases created, except for tempdb.

    The current setting of this option can be determined by examining the is_read_committed_snapshot_on column in the sys.databases catalog view.

WITH <termination>::=

Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there is any lock on the database. Only one termination clause can be specified, and it follows the SET clauses.

Note

Not all database options use the WITH <termination> clause. For more information, see the table under "Setting Options" in the Remarks section.

  • ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
    Specifies whether to roll back after the specified number of seconds or immediately.
  • NO_WAIT
    Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

Remarks

To remove a database, use DROP DATABASE.

To rename a database, use the MODIFY NAME = new_database_name option with ALTER DATABASE.

To decrease the size of a database, use DBCC SHRINKDATABASE.

You cannot add or remove a file while a BACKUP statement is running.

A maximum of 32,767 files and 32,767 filegroups can be specified for each database.

The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction. For more information, see Autocommit Transactions.

In SQL Server 2005, the state of a database file (for example, online or offline), is maintained independently from the state of the database. For more information, see File States. The state of the files within a filegroup determines the availability of the whole filegroup. For a filegroup to be available, all files within the filegroup must be online. If a filegroup is offline, any try to access the filegroup by an SQL statement will fail with an error. When you build query plans for SELECT statements, the query optimizer avoids nonclustered indexes and indexed views that reside in offline filegroups. This enables these statements to succeed. However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup will fail.

When a database is in the RESTORING state, most ALTER DATABASE statements will fail. The exception is setting database mirroring options. A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file. For more information, see Responding to SQL Server Restore Errors Caused by Damaged Backups.

Setting Options

To retrieve current settings for database options, use the sys.databases catalog view or DATABASEPROPERTYEX. For a list of default values assigned to the database when it is first created, see Setting Database Options.

After you set a database option, the modification takes effect immediately.

To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database.

Not all database options use the WITH <termination> clause or can be specified in combination with other options. The following table lists these options and their option and termination status.

Options category Can be specified with other options Can use the WITH <termination> clause

<db_state_option>

Yes

Yes

<db_user_access_option>

Yes

Yes

db_update_option>

Yes

Yes

<external_access_option>

Yes

No

<cursor_option>

Yes

No

<auto_option>

Yes

No

<sql_option>

Yes

No

<recovery_option>

Yes

No

<database_mirroring_option>

No

No

ALLOW_SNAPSHOT_ISOLATION

No

No

READ_COMMITTED_SNAPSHOT

No

Yes

<service_broker_option>

Yes

No

DATE_CORRELATION_OPTIMIZATION

Yes

Yes

<parameterization_option>

Yes

Yes

The plan cache for the instance of SQL Server is cleared by setting one of the following options:

OFFLINE

READ_WRITE

ONLINE

MODIFY FILEGROUP DEFAULT

MODIFY_NAME

MODIFY FILEGROUP READ_WRITE

COLLATE

MODIFY FILEGROUP READ_ONLY

READ_ONLY

 

Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.

Moving Files

In SQL Server 2005, you can move system or user-defined data and log files by specifying the new location in FILENAME. This may be useful in the following scenarios:

  • Failure recovery. For example, the database is in suspect mode or shutdown caused by hardware failure.
  • Planned relocation.
  • Relocation for scheduled disk maintenance.

For more information, see Moving Database Files.

Initializing Files

By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:

  • Create a database.
  • Add files to an existing database.
  • Increase the size of an existing file.
  • Restore a database or filegroup.

In SQL Server 2005, data files can be initialized instantaneously. This enables for fast execution of these file operations. For more information, see Database File Initialization.

Changing the Database Collation

Before you apply a different collation to a database, make sure that the following conditions are in place:

  1. You are the only one currently using the database.
  2. No schema-bound object depends on the collation of the database.
    If the following objects, which depend on the database collation, exist in the database, the ALTER DATABASE database_name COLLATE statement will fail. SQL Server will return an error message for each object blocking the ALTER action:
    • User-defined functions and views created with SCHEMABINDING.
    • Computed columns.
    • CHECK constraints.
    • Table-valued functions that return tables with character columns with collations inherited from the default database collation.
  3. Changing the database collation does not create duplicates among any system names for the database objects.
    The following namespaces may cause the failure of a database collation change if duplicate names result from the changed collation:
    • Object names such as a procedure, table, trigger, or view.
    • Schema names
    • Principals such as a group, role, or user.
    • Scalar-type names such as system and user-defined types.
    • Full-text catalog names.
    • Column or parameter names within an object.
    • Index names within a table.
      Duplicate names resulting from the new collation will cause the change action to fail, and SQL Server will return an error message specifying the namespace where the duplicate was found.

Viewing Database Information

You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups. For more information, see Viewing Database Metadata.

Permissions

Requires ALTER permission on the database.

Examples

A. Adding a file to a database

The following example adds a 5-MB data file to the AdventureWorks database.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = '''+ @data_path + 't1dat2.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)'
);
GO

B. Adding a filegroup with two files to a database

The following example creates the filegroup Test1FG1 in the AdventureWorks database and adds two 5-MB files to the filegroup.

USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = '''+ @data_path + 't1dat3.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = '''+ @data_path + 't1dat4.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1'
);
GO

C. Adding two log files to a database

The following example adds two 5-MB log files to the AdventureWorks database.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = '''+ @data_path + 'test2log.ldf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = '''+ @data_path + 'test3log.ldf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)'
);
GO

D. Removing a file from a database

The following example removes one of the files added in example B.

USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO

E. Modifying a file

The following example increases the size of one of the files added in example B.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

F. Moving a file to a new location

The following example moves the Test1dat2 file created in example A to a new directory.

Note

You must physically move the file to the new directory before running this example. Afterward, stop and start the instance of SQL Server or take the AdventureWorks database OFFLINE and then ONLINE to implement the change.

USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. Moving tempdb to a new location

The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.

  1. Determine the logical file names of the tempdb database and their current location on disk.

    SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

  2. Change the location of each file by using ALTER DATABASE.

    USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO

  3. Stop and restart the instance of SQL Server.

  4. Verify the file change.

    SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

  5. Delete the tempdb.mdf and templog.ldf files from their original location.

H. Making a filegroup the default

The following example makes the Test1FG1 filegroup created in example B the default filegroup. Then, the default filegroup is reset to the PRIMARY filegroup. Note that PRIMARY must be delimited by brackets or quotation marks.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. Setting options on a database

The following example sets the recovery model and data page verification options for the AdventureWorks sample database.

USE master;
GO
ALTER DATABASE AdventureWorks 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

J. Setting the database to READ_ONLY

Changing the state of a database or filegroup to READ_ONLY or READ_WRITE requires exclusive access to the database. The following example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the AdventureWorks database to READ_ONLY and returns access to the database to all users.

Note

This example uses the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the AdventureWorks sample database will be immediately disconnected.

USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

K. Enabling snapshot isolation on a database

The following example enables the snapshot isolation framework option for the AdventureWorks database.

USE AdventureWorks;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
USE master;
GO
ALTER DATABASE AdventureWorks
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO

The result set shows that the snapshot isolation framework is enabled.

name            snapshot_isolation_state  description
--------------- ------------------------  -----------
AdventureWorks  1                         ON

L. Creating a database mirroring session with a witness

Setting up database mirroring with a witness requires configuring security and preparing the mirror database, and also using ALTER DATABASE to set the partners. For an example of the complete setup process, see Setting Up Database Mirroring.

M. Manually failing over a database mirroring session

Manual failover can be initiated from either database mirroring partner. Before failing over, you should verify that the server you believe to be the current principal server actually is the principal server. For example, for the AdventureWorks database, on that server instance that you think is the current principal server, execute the following query:

SELECT db.name, m.mirroring_role_desc 
FROM sys.database_mirroring m 
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks' 
GO

If the server instance is in fact the principal, the value of mirroring_role_desc is Principal. If this server instance were the mirror server, the SELECT statement would return Mirror.

The following example assumes that the server is the current principal.

  1. Manually fail over to the database mirroring partner:

    ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
GO

  2. To verify the results of the failover on the new mirror, execute the following query:

    SELECT name, mirroring_role_desc 
FROM sys.databases WHERE name = N'AdventureWorks';
GO

    The current value of mirroring_role_desc is now Mirror.

See Also

Reference

CREATE DATABASE (Transact-SQL)
DATABASEPROPERTYEX (Transact-SQL)
DROP DATABASE (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_configure (Transact-SQL)
sp_spaceused (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
sys.database_mirroring_witnesses (Transact-SQL)
sys.data_spaces (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.master_files (Transact-SQL)

Other Resources

Enabling Row Versioning-Based Isolation Levels
System Databases

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:
  • Added information about options that clear the plan cache to the "Setting Options" section in Remarks and the definition of AUTO_CLOSE.
  • Added information about using the AUTO_UPDATE_STATISTICS_ASYNC option in single-user mode in the definition of SINGLE_USER.

14 April 2006
Changed content:
  • Updated the description of FAILOVER option to indicate that it requires master as the database context.
  • Added an Important note to the introduction of the "<database_mirroring_option>" section.
  • Under ALLOW_SNAPSHOT_ISOLATION argument, updated information about determining the state of snapshot-isolation transactions in the database, and how that state affects the behavior of SQL Server when this option is changed.
  • Corrected the definition of DATE_CORRELATION_OPTIMIZATION.

5 December 2005
New content:
  • Added a note to the definition of ENABLE_BROKER.
  • Added recommendation for modifying the PAGE_VERIFY option on upgraded databases.
Changed content:
  • Removed the SUPPLEMENTAL_LOGGING option.
  • Corrected example G.
  • Corrected information on the TRUSTWORTHY option in system databases.
  • Updated the definition of READ_COMMITTED_SNAPSHOT to indicate single-user mode is not required.
  • Updated the definition of <db_state_option> to indicate the options OFFLINE, ONLINE, and EMERGENCY cannot be set when the database is in the RESTORING state.