Monday , September 25 2017
Home / MSSQL DBA / Setting Database Options

Setting Database Options

S

Setting Database Options

A number of database-level options that determine the characteristics of the database can be set for each database. Only the SA, database owner, db creator fixed server roles and db_owner fixed database roles can modify these options. These options are unique to each database and do not affect other databases. The database options can be set by using the SET clause of the ALTER DATABASE statement, the sp_dboption system stored procedure or, in some cases, SQL Server Enterprise Manager.

Note  Server-wide settings are set using the sp_configure system stored procedure or SQL Server Enterprise Manager

After you set a database option, a checkpoint is automatically issued that causes the modification to take effect immediately.

To change the default values for any of the database options for newly created databases, change the appropriate database option in the model database. For example, if you want the default setting of the AUTO_SHRINK database option to be ON for any new databases subsequently created, set the AUTO_SHRINK option for model to ON.

There are five categories of database options:

  • Automatic options
  • Cursor options
  • Recovery options
  • SQL options
  • State options
  • Service Broker

Containment :- Containment type

Specify none or partial to designate if this is a contained database. For more information about contained databases

Auto Options

Auto Close

Specify whether the database shuts down cleanly and frees resources after the last user exits. Possible values are True and False. When True, the database is shut down cleanly and its resources are freed after the last user logs off.

Auto Create Statistics

Specify whether the database automatically creates missing optimization statistics. Possible values are True and False. When True, any missing statistics needed by a query for optimization are automatically built during optimization.

Auto Shrink

Specify whether the database files are available for periodic shrinking. Possible values are True and False..

Auto Update Statistics

Specify whether the database automatically updates out-of-date optimization statistics. Possible values are True and False. When True, any out-of-date statistics needed by a query for optimization are automatically built during optimization.

Auto Update Statistics Asynchronously

When True, 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.

When False, 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 True has no effect unless Auto Update Statistics is also set to True.

Cursor Options

Cursor options control cursor behavior and scope.

CURSOR_CLOSE_ON_COMMIT

When set to ON, any open cursors are closed automatically (in compliance with SQL-92) when a transaction is committed. By default, this setting is OFF and cursors remain open across transaction boundaries, closing only when the connection is closed or when they are explicitly closed.

Connection-level settings (set 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 SQL Server.

The status of this option can be determined by examining the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTYEX function.

CURSOR_DEFAULT LOCAL | GLOBAL

When CURSOR_DEFAULT LOCAL is set, and a cursor is not defined as GLOBAL when it is 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 terminates, unless it was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

When CURSOR_DEFAULT GLOBAL is set, 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. CURSOR_DEFAULT GLOBAL is the default setting.

The status of this option can be determined by examining the IsLocalCursorsDefault property of the DATABASEPROPERTYEX function.

Recovery Options

Recovery options controls the recovery model for the database.

RECOVERY FULL | BULK_LOGGED | SIMPLE

When FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.

When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery.

When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup.

TORN_PAGE_DETECTION

This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.

When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the page was written incorrectly; a torn page is detected. Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery.

Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.

Note  Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.

If a torn page is detected, an I/O error is raised and the connection is killed. If the torn page is detected during recovery, the database is also marked suspect. The database backup should be restored, and any transaction log backups applied, because it is physically inconsistent.

By default, TORN_PAGE_DETECTION is ON.

The current setting of this option can be determined by examining the IsTornPageDetectionEnabled property of DATABASEPROPERTYEX.

State

Database Read Only

Specify whether the database is read only. Possible values are True and False. When True, users can only read data in the database. Users cannot modify the data or database objects; however, the database itself can be deleted using the DROP DATABASE statement. The database cannot be in use when a new value for the Database Read Only option is specified. The master database is the exception, and only the system administrator can use master while the option is being set.

Database State

View the current state of the database. It is not editable

Restrict Access

Specify which users may access the database. Possible values are:

  • Multiple

The normal state for a production database, allows multiple users to access the database at once.

  • Single

Used for maintenance actions, only one user is allowed to access the database at once.

  • Restricted

Only members of the db_owner, dbcreator, or sysadmin roles can use the database.

Encryption Enabled

When True, this database is enabled for database encryption. A Database Encryption Key is required for encryption.

OFFLINE | ONLINE

When OFFLINE is specified, the database is closed and shutdown cleanly and marked offline. The database cannot be modified while the database is offline.

When ONLINE is specified, the database is open and available for use. ONLINE is the default setting.

The status of this option can be determined by examining the Status property of the DATABASEPROPERTYEX function.

READ_ONLY | READ_WRITE

When READ_ONLY is specified, the database is in read-only mode. Users can retrieve data from the database, but cannot modify the data. 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 takes place in read-only databases, which can result in faster query performance.

When READ_WRITE is specified, users can retrieve and modify data. READ_WRITE is the default setting.

The status of this option can be determined by examining the Updateability property of the DATABASEPROPERTYEX function.

SINGLE_USER | RESTRICTED_USER | MULTI_USER

SINGLE_USER allows one user at a time to connect to the database. All other user connections are broken. The timeframe for breaking the connection is controlled by the termination clause of the ALTER DATABASE statement. New connection attempts are refused. The database remains in SINGLE_USER mode even if the user who set the option logs off. At that point, a different user (but only one) can connect to the database.

To allow multiple connections, the database must be changed to RESTRICTED_USER or MULTI_USER mode.

RESTRICTED_USER allows only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but it does not limit their number. Users who are not members of these roles are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. Moreover, new connection attempts by unqualified users are refused.

MULTI_USER allows all users with the appropriate permissions to connect to the database. MULTI_USER is the default setting.

The status of this option can be determined by examining the UserAccess property of the DATABASEPROPERTYEX function.

Recovery

Page Verify

Specify the option used to discover and report incomplete I/O transactions caused by disk I/O errors. Possible values are None, TornPageDetection, and Checksum.

Target Recovery Time (Seconds)

Specifies the maximum bound on the time, expressed in seconds, to recover the specified database in the event of a crash. For more information

FILESTREAM

FILESTREAM Directory Name

Specify the directory name for the FILESTREAM data associated with the selected database.

FILESTREAM Non-transacted Access

Specify one of the following options for non-transactional access through the file system to FILESTREAM data stored in FileTables: OFF, READ_ONLY, or FULL. If FILESTREAM is not enabled on the server, this value is set to OFF and is disabled.

Read More:
Samsung Galaxy Note Edge review
Microsoft SQL Server 2012 Database new features
10,000 Web Site Visitors In One Month:Guaranteed

Comments

comments

Check Also

SQL Server Error Logs

SQL Server Error Logs Detailed Information

SQL Server Error Logs * Error Logs maintains events raised by SQL Server server or …

Leave a Reply

Your email address will not be published. Required fields are marked *