ALTER DATABASE の SET オプション (Transact-SQL)

このトピックでは、データベース オプションの設定に関連した ALTER DATABASE 構文について説明します。その他の ALTER DATABASE 構文については、「ALTER DATABASE (Transact-SQL)」を参照してください。データベース ミラーリングおよび互換性レベルは SET オプションですが、長くなるため、別のトピックで説明しています。詳細については、「ALTER DATABASE データベース ミラーリング (Transact-SQL)」および「ALTER DATABASE 互換性レベル (Transact-SQL)」を参照してください。

Transact-SQL 構文表記規則

構文

ALTER DATABASE database_name 
SET 
{
    { <optionspec> [ ,...n ] [ WITH <termination> ] }
}

<optionspec>::= 
{
    <auto_option> 
  | <change_tracking_option> 
  | <cursor_option> 
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option> 
  | <db_user_access_option>
  | <external_access_option>
  | <parameterization_option>
  | <recovery_option> 
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option> 
}

<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 }
}

<change_tracking_option> ::=
{
  CHANGE_TRACKING { 
        = ON [ <change_tracking_option_list > ] |
    <change_tracking_option_list> |
        = OFF
  }
}

<change_tracking_option_list> ::=
{
    ( <change_tracking_option> | <change_tracking_option_list> , 
    <change_tracking_option> )
}
  
<change_tracking_option> ::=
{
  AUTO_CLEANUP = { ON | OFF } 
  | CHANGE_RETENTION = { retention_period { DAYS | HOURS | MINUTES } ]
}

<cursor_option> ::= 
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF } 
  | CURSOR_DEFAULT { LOCAL | GLOBAL } 
}

<database_mirroring_option>ALTER DATABASE Database Mirroring<date_correlation_optimization_option> ::=
{
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF }

<db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

<external_access_option> ::=
{
    DB_CHAINING { ON | OFF }

  | TRUSTWORTHY { ON | OFF }
}
<parameterization_option> ::=
{
    PARAMETERIZATION { SIMPLE | FORCED }
}

<recovery_option> ::= 
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE } 
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<service_broker_option> ::=
{
    ENABLE_BROKER
  | DISABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
  | HONOR_BROKER_PRIORITY { ON | OFF}
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<sql_option> ::= 
{
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
  | RECURSIVE_TRIGGERS { ON | OFF } 
}

<termination>::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

引数

<auto_option>::=

自動オプションを制御します。

• database_name
  変更するデータベースの名前を指定します。

• AUTO_CLOSE { ON | OFF }

  • ON
    最後のユーザーが終了した後、データベースは正常にシャットダウンされ、そのリソースは解放されます。

    ユーザーが再びそのデータベースを使用しようとすると、そのデータベースが自動的に再度開かれます。たとえば USE database_name ステートメントを実行する場合などです。AUTO_CLOSE が ON に設定されている状態でデータベースが正常にシャットダウンした場合は、次回データベース エンジンが再起動したとき、ユーザーがデータベースを使用するまでは、データベースは開きません。 

  • OFF
    最後のユーザーが終了した後も、データベースは開いたままになります。

  AUTO_CLOSE オプションを使用すると、データベース ファイルを通常のファイルとして管理できるため、デスクトップ データベースには便利なオプションです。普通のファイルと同じように、移動やコピーによってバックアップを作成したり、他のユーザーに電子メールで送信することができます。

  注意
  SQL Server の以前のバージョンでは、AUTO_CLOSE は、データベース エンジンへの接続と切断を繰り返すアプリケーションがデータベースにアクセスした場合に、パフォーマンスを低下させる可能性のある同期プロセスでした。SQL Server 2005 以降では、AUTO_CLOSE が非同期プロセスになったため、データベースを開いたり閉じたりする操作を繰り返しても、パフォーマンスは低下しません。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_auto_close_on 列、または DATABASEPROPERTYEX 関数の IsAutoClose プロパティを調べます。

  注意
  AUTO_CLOSE が ON の場合、データベースからデータを取得できないため、sys.databases カタログ ビューの一部の列、および DATABASEPROPERTYEX 関数は、NULL を返します。これを解決するには、USE ステートメントを実行してデータベースを開きます。

  注意
  データベースをミラー化するには、AUTO_CLOSE を OFF に設定する必要があります。

  データベースを AUTOCLOSE = ON に設定すると、データベースの自動シャットダウンを開始する操作によって、SQL Server のインスタンスのプラン キャッシュが消去されます。プラン キャッシュが消去されると、後続のすべての実行プランが再コンパイルされ、場合によっては、クエリ パフォーマンスが一時的に急激に低下します。SQL Server 2005 Service Pack 2 では、プラン キャッシュ内のキャッシュストアが消去されるたびに、"SQL Server は、一部のデータベース メンテナンス操作または再構成操作により、'%s' キャッシュストア (プラン キャッシュの一部) のキャッシュストア フラッシュを %d 個検出しました。" という情報メッセージが SQL Server エラー ログに記録されます。このメッセージは、5 分間隔でログに記録されるようになっているので、その期間内に発生したキャッシュのフラッシュが記録されます。

• AUTO_CREATE_STATISTICS { ON | OFF }

  • ON
    クエリ プランを改善してクエリのパフォーマンスを向上させるために、クエリ オプティマイザーが必要に応じてクエリ述語内の列に対して 1 列ずつ統計を作成します。これらの統計は、クエリ オプティマイザーがクエリをコンパイルするときに作成されます。1 列ずつの統計は、まだ既存の統計オブジェクトの最初の列になっていない列についてのみ作成されます。

    既定値は ON です。ほとんどのデータベースで既定の設定を使用することをお勧めします。

  • OFF
    クエリ オプティマイザーがクエリをコンパイルするときにクエリ述語内の列の 1 列ずつの統計が作成されません。このオプションを OFF に設定すると、最適ではないクエリ プランが作成されて、クエリのパフォーマンスが低下することがあります。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_auto_create_stats_on 列、または DATABASEPROPERTYEX 関数の IsAutoCreateStatistics プロパティを調べます。

  詳細については、「クエリのパフォーマンスを向上させるための統計の使用」の「データベース全体の統計オプションの使用」を参照してください。

• AUTO_SHRINK { ON | OFF }

  • ON
    データベース ファイルを定期的な圧縮処理の対象とします。

    データ ファイルとログ ファイルの両方を、自動的に圧縮できます。AUTO_SHRINK は、データベースが単純復旧モデルに設定されている場合や、ログがバックアップされている場合にのみ、トランザクション ログのサイズを圧縮します。OFF に設定すると、未使用領域の定期チェックの際に、データベース ファイルは自動的に圧縮されません。

    AUTO_SHRINK オプションを使用すると、ファイル領域の 25% を超える領域が未使用の場合にファイルが圧縮されます。未使用領域がファイル領域の 25% になるサイズ、またはそのファイルが作成された時点でのサイズの、どちらか大きい方のサイズまで圧縮されます。

    読み取り専用データベースは圧縮できません。

  • OFF
    データベース ファイルは、未使用領域の定期的なチェックの際に、自動的に圧縮されません。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_auto_shrink_on 列、または DATABASEPROPERTYEX 関数の IsAutoShrink プロパティを調べます。

• AUTO_UPDATE_STATISTICS { ON | OFF }

  • ON
    クエリで使用される統計が古くなっている可能性がある場合にクエリ オプティマイザーによって更新されるように指定します。挿入、更新、削除、またはマージの各操作によってテーブルまたはインデックス付きビューのデータの分布が変わると、統計は古くなったと判断されます。クエリ オプティマイザーでは、統計が前回更新されてから発生したデータ変更の数をカウントし、その変更の数をしきい値と比較することで、統計が古くなっている可能性がないかを判断します。このしきい値は、テーブルまたはインデックス付きビューの行数に基づいて決められます。

    クエリ オプティマイザーによる古い統計の確認は、クエリをコンパイルする前と、キャッシュされたクエリ プランを実行する前に行われます。クエリをコンパイルする前は、クエリ オプティマイザーで、クエリ述語内の列、テーブル、およびインデックス付きビューを使用して古くなっている可能性がある統計が判断されます。キャッシュされたクエリ プランを実行する前は、データベース エンジンで、クエリ プランが最新の統計を参照しているかどうかが確認されます。

    AUTO_UPDATE_STATISTICS オプションは、インデックスに対して作成された統計、クエリ述語内の列に対して 1 列ずつ作成された統計、および CREATE STATISTICS ステートメントを使用して作成された統計に適用されます。また、フィルター選択された統計情報にも適用されます。

    既定値は ON です。ほとんどのデータベースで既定の設定を使用することをお勧めします。

    統計を同期的に更新するか非同期的に更新するかを指定するには、AUTO_UPDATE_STATISTICS_ASYNC オプションを使用します。

  • OFF
    クエリで使用される統計が古くなっている可能性がある場合にクエリ オプティマイザーによって更新されないように指定します。このオプションを OFF に設定すると、最適ではないクエリ プランが作成されて、クエリのパフォーマンスが低下することがあります。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_auto_update_stats_on 列、または DATABASEPROPERTYEX 関数の IsAutoUpdateStatistics プロパティを調べます。

  詳細については、「クエリのパフォーマンスを向上させるための統計の使用」の「データベース全体の統計オプションの使用」を参照してください。

• AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

  • ON
    AUTO_UPDATE_STATISTICS オプションの統計の更新を非同期更新にするように指定します。クエリ オプティマイザーは、統計の更新が完了するのを待たずにクエリをコンパイルします。

    AUTO_UPDATE_STATISTICS が ON に設定されていなければ、このオプションを ON に設定しても、効果はありません。

    既定では、AUTO_UPDATE_STATISTICS_ASYNC オプションは OFF に設定されており、クエリ オプティマイザーによる統計の更新は同期更新になります。

  • OFF
    AUTO_UPDATE_STATISTICS オプションの統計の更新を同期更新にするように指定します。クエリ オプティマイザーは、統計の更新が完了するのを待ってからクエリをコンパイルします。

    AUTO_UPDATE_STATISTICS が ON に設定されていなければ、このオプションを OFF に設定しても、効果はありません。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_auto_update_stats_async_on 列を調べます。

  統計の同期更新と非同期更新をそれぞれどのような場合に使用するのかについては、「クエリのパフォーマンスを向上させるための統計の使用」の「データベース全体の統計オプションの使用」を参照してください。

<change_tracking_option>::=

変更の追跡のオプションを制御します。変更の追跡の有効化、オプションの設定、オプションの変更、および変更の追跡の無効化が可能です。例については、後の「例」のセクションを参照してください。

• ON
  データベースの変更の追跡を有効にします。変更の追跡を有効にすると、AUTO CLEANUP オプションと CHANGE RETENTION オプションも設定できます。

• AUTO_CLEANUP = { ON | OFF }

  • ON
    指定した保有期間を過ぎると、変更追跡情報が自動的に削除されます。

  • OFF
    変更追跡データがデータベースから削除されません。

• CHANGE_RETENTION =retention_period { DAYS | HOURS | MINUTES }
  データベースに変更追跡情報を保持する最低限の期間を指定します。データは、AUTO_CLEANUP の値が ON のときにのみ削除されます。

  retention_period は、保有期間の数値部分を指定する整数です。

  既定の保有期間は 2 日です。最小保有期間は 1 分です。

• OFF
  データベースの変更の追跡を無効にします。データベースの変更の追跡を無効にする前に、すべてのテーブルで変更の追跡を無効にする必要があります。

<cursor_option>::=

カーソル オプションを制御します。

• CURSOR_CLOSE_ON_COMMIT { ON | OFF }

  • ON
    トランザクションのコミットまたはロールバック時に開いていたすべてのカーソルが閉じます。

  • OFF
    トランザクションがコミットされても、カーソルは開いたままになります。トランザクションをロールバックすると、INSENSITIVE または STATIC として定義されているカーソルを除き、すべてのカーソルが閉じます。

  SET ステートメントを使用した接続レベルの設定は、CURSOR_CLOSE_ON_COMMIT の既定のデータベース設定よりも優先されます。既定では、ODBC クライアントと OLE DB クライアントは、SQL Server のインスタンスに接続するときに、セッションの CURSOR_CLOSE_ON_COMMIT を OFF に設定する接続レベルの SET ステートメントを実行します。詳細については、「SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL)」を参照してください。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_cursor_close_on_commit_on 列、または DATABASEPROPERTYEX 関数の IsCloseCursorsOnCommitEnabled プロパティを調べます。

• CURSOR_DEFAULT { LOCAL | GLOBAL }
  カーソルのスコープを LOCAL と GLOBAL のどちらにするかを制御します。

  • LOCAL
    LOCAL を指定すると、カーソルが作成時に GLOBAL として定義されていない場合、カーソルのスコープは、そのカーソルが作成されたバッチ、ストアド プロシージャ、またはトリガーに限定されます。カーソル名はこのスコープ内だけで有効です。カーソルは、バッチ、ストアド プロシージャ、またはトリガー内のローカル カーソル変数からか、ストアド プロシージャの OUTPUT パラメーターから参照できます。OUTPUT パラメーター内でカーソルが戻されない限り、バッチ、ストアド プロシージャ、またはトリガーが終了すると、カーソルは暗黙的に割り当てを解除されます。カーソルが OUTPUT パラメーターで戻された場合は、カーソルを参照している最後の変数が割り当て解除されるか、スコープ外になったときに、カーソルの割り当てが解除されます。

  • GLOBAL
    GLOBAL を指定すると、カーソルが作成時に LOCAL として定義されていない場合、カーソルのスコープはその接続に対してグローバルになります。カーソル名は、その接続によって実行されるストアド プロシージャやバッチの中で参照できます。

  接続が切断されたときだけカーソルが暗黙的に割り当てを解除されます。詳細については、「DECLARE CURSOR (Transact-SQL)」を参照してください。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_local_cursor_default 列、または DATABASEPROPERTYEX 関数の IsLocalCursorsDefault プロパティを調べます。

<database_mirroring>

引数の説明については、「ALTER DATABASE データベース ミラーリング (Transact-SQL)」を参照してください。

<date_correlation_optimization_option> ::=

date_correlation_optimization オプションを制御します。

• DATE_CORRELATION_OPTIMIZATION { ON | OFF }

  • ON
    SQL Server は、データベース内に FOREIGN KEY 制約でリンクされ、datetime 列を含んでいる複数のテーブルがあれば、リンクされているテーブル間の相関統計を保持します。詳細については、「相関した datetime 列にアクセスするクエリの最適化」を参照してください。

  • OFF
    相関統計は保持されません。

  DATE_CORRELATION_OPTIMIZATION を ON に設定するには、データベースに対するアクティブな接続が、ALTER DATABASE ステートメントを実行する接続の他には存在しないようにします。設定後、複数の接続がサポートされます。

  このオプションの現在の設定を確認するには、sys.databases カタログ ビューの is_date_correlation_on 列を調べます。

<db_encryption_option>::=

データベース暗号化の状態を制御します。

• ENCRYPTION {ON | OFF}
  データベースを暗号化する (ON) か、暗号化しない (OFF) かを設定します。データベースの暗号化の詳細については、「透過的なデータ暗号化 (TDE) について」を参照してください。

データベース レベルで暗号化を有効にすると、すべてのファイル グループが暗号化されます。すべての新しいファイル グループに、その暗号化プロパティが継承されます。データベースに READ ONLY に設定されているファイル グループがあると、データベースの暗号化操作は失敗します。

データベースの暗号化の状態を確認するには、sys.dm_database_encryption_keys 動的管理ビューを使用します。

<db_state_option>::=

データベースの状態を制御します。

• OFFLINE
  データベースが閉じ、正常にシャットダウンされ、オフラインとしてマークされます。データベースがオフラインのときはデータベースを変更できません。

• ONLINE
  データベースが開き、使用可能になります。

• EMERGENCY
  データベースは READ_ONLY としてマークされ、ログ記録が無効になり、アクセスが sysadmin 固定サーバー ロールのメンバーに制限されます。EMERGENCY は、主にトラブルシューティングの目的に使用されます。たとえば、破損したログ ファイルが原因で問題ありとマークされたデータベースを EMERGENCY 状態に設定できます。これにより、システム管理者はデータベースに読み取り専用でアクセスできるようになります。データベースを EMERGENCY 状態に設定できるのは、sysadmin 固定サーバー ロールのメンバーだけです。

このオプションの状態を確認するには、sys.databases カタログ ビューの state 列と state_desc 列、またはDATABASEPROPERTYEX 関数の Status プロパティを調べます。詳細については、「データベースの状態」を参照してください。

RESTORING とマークされたデータベースを OFFLINE、ONLINE、または EMERGENCY に設定することはできません。データベースが RESTORING 状態になるのは、アクティブな復元操作中や、バックアップ ファイルの破損によりデータベースまたはログ ファイルの復元操作が失敗した場合などです。詳細については、「バックアップの破損による SQL Server 復元エラーの対応」を参照してください。

<db_update_option>::=

データベースで更新を許可するかどうかを制御します。

• READ_ONLY
  ユーザーは、データベースのデータを読み取ることができますが、変更はできません。

• READ_WRITE
  データベースに対して読み取りおよび書き込み操作を行うことができます。

この状態を変更するには、データベースに対する排他的アクセスが必要になります。詳細については、SINGLE_USER 句を参照してください。

<db_user_access_option> ::=

データベースへのユーザー アクセスを制御します。

• SINGLE_USER
  一度に 1 人のユーザーだけがデータベースにアクセスできます。SINGLE_USER が指定されており、他のユーザーがデータベースに接続している場合には、指定したデータベースからすべてのユーザーが接続解除するまで、ALTER DATABASE ステートメントはブロックされます。この動作を無効にする場合は、WITH <termination> 句を参照してください。

  このオプションを設定したユーザーがログオフしても、データベースは SINGLE_USER モードのままです。そのユーザーがログオフした時点で、他のユーザーが 1 人だけデータベースに接続できます。

  データベースを SINGLE_USER に設定する前に、AUTO_UPDATE_STATISTICS_ASYNC オプションが OFF に設定されていることを確認します。ON に設定されていると、統計の更新に使用されるバックグラウンド スレッドによってデータベースへの接続が使用されるため、シングル ユーザー モードではデータベースにアクセスできなくなります。このオプションの状態を表示するには、sys.databases カタログ ビューの is_auto_update_stats_async_on 列に対してクエリを実行します。このオプションが ON に設定されている場合、次の作業を行います。

  1. AUTO_UPDATE_STATISTICS_ASYNC を OFF に設定します。

  2. sys.dm_exec_background_job_queue 動的管理ビューのクエリを実行することにより、アクティブな非同期の統計ジョブがあるかどうかを確認します。

  アクティブなジョブがある場合、それらのジョブが完了するまで待つか、KILL STATS JOB を使用して手動でジョブを終了します。

• RESTRICTED_USER
  RESTRICTED_USER モードでは、db_owner 固定データベース ロールと、dbcreator および sysadmin 固定サーバー ロールのメンバーだけがデータベースに接続できます。ただし、接続ユーザー数に制限はありません。データベースに対するすべての接続は、ALTER DATABASE ステートメントの終了句で指定した時間枠内に接続解除されます。データベースが RESTRICTED_USER 状態に移行すると、資格のないユーザーによる接続の試みは拒否されます。

• MULTI_USER
  データベースに接続するための適切な権限を持つすべてのユーザーが許可されます。

このオプションの状態を確認するには、sys.databases カタログ ビューの user_access 列、または DATABASEPROPERTYEX 関数の UserAccess プロパティを調べます。

<external_access_option>::=

別のデータベースのオブジェクトなど、外部リソースからデータベースにアクセスできるかどうかを制御します。

• DB_CHAINING { ON | OFF }

  • ON
    データベースは、複数データベースの組み合わせ所有権の連係でソース データベースまたはターゲット データベースになることができます。

  • OFF
    データベースは、複数データベースの組み合わせ所有権に参加できません。

  重要
  SQL Server のインスタンスでは、cross db ownership chaining サーバー オプションが 0 (OFF) の場合に、この設定が認識されます。cross db ownership chaining が 1 (ON) の場合は、このオプションの値にかかわらず、すべてのユーザー データベースが複数データベースの組み合わせ所有権に参加できます。このオプションは、sp_configure を使用して設定します。

  このオプションを設定するには、データベースに対する CONTROL SERVER 権限が必要です。master、model、tempdb の各システム データベースでは、DB_CHAINING オプションを設定できません。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_db_chaining_on 列を調べます。

  詳細については、「所有権の継承」を参照してください。

• TRUSTWORTHY { ON | OFF }

  • ON
    権限借用コンテキストを使用するデータベース モジュール (ユーザー定義関数やストアド プロシージャなど) は、データベース外部のリソースにアクセスできます。

  • OFF
    権限借用コンテキスト内にあるデータベース モジュールは、データベース外部のリソースにアクセスできません。

  データベースをアタッチすると必ず TRUSTWORTHY が OFF に設定されます。

  既定では、msdb データベースを除くすべてのシステム データベースで TRUSTWORTHY は OFF に設定されています。model および tempdb データベースでは、この値は変更できません。master データベースでは、TRUSTWORTHY オプションを ON に設定しないことをお勧めします。

  このオプションを設定するには、データベースに対する CONTROL SERVER 権限が必要です。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_trustworthy_on 列を調べます。

<parameterization_option> ::=

パラメーター化オプションを制御します。

• PARAMETERIZATION { SIMPLE | FORCED }

  • SIMPLE
    クエリは、データベースの既定の動作に基づいてパラメーター化されます。詳細については、「簡易パラメータ化」を参照してください。

  • FORCED
    SQL Server は、データベース内にあるすべてのクエリをパラメーター化します。詳細については、「強制パラメータ化」を参照してください。

  このオプションの現在の設定を確認するには、sys.databases カタログ ビューの is_parameterization_forced 列を調べます。

<recovery_option> ::=

データベース復旧オプションおよびディスク I/O エラー チェックを制御します。

• FULL
  メディア障害が発生した後に、トランザクション ログのバックアップを使用して、完全復旧を行います。データ ファイルが破損した場合、メディアの復旧によって、コミットされたすべてのトランザクションを復元できます。詳細については、「完全復旧モデルでのバックアップ」を参照してください。

• BULK_LOGGED
  メディア障害が発生した後に、特定の大規模操作または一括操作において、パフォーマンスが最もよく、ログ領域の使用量が最も小さくなるようにして、復旧を行います。一括ログ記録される操作の種類の詳細については、「最小ログ記録が可能な操作」を参照してください。BULK_LOGGED 復旧モデルでは、これらの操作に関するログ記録は最小になります。詳細については、「一括ログ復旧モデルでのバックアップ」を参照してください。

• SIMPLE
  最小のログ領域を使用する、単純なバックアップ方法が実行されます。ログ領域は、サーバー障害の復旧での使用が終わると自動的に再利用できるようになります。詳細については、「単純復旧モデルでのバックアップ」を参照してください。

  重要
  単純復旧モデルは、他の 2 つのモデルよりも管理が簡単ですが、データ ファイルが破損した場合にデータが失われる危険性が高くなります。前回のデータベースのバックアップ作成や差分バックアップ作成の後に行った変更はすべて失われるため、手作業で入力し直す必要があります。

既定の復旧モデルは、model データベースの復旧モデルによって決定されます。適切な復旧モデルの選択の詳細については、「データベースの復旧モデルの選択」を参照してください。

このオプションの状態を確認するには、sys.databases カタログ ビューの recovery_model 列と recovery_model_desc 列、または DATABASEPROPERTYEX 関数の Recovery プロパティを調べます。

• TORN_PAGE_DETECTION { ON | OFF }

  • ON
    データベース エンジンによって不完全なページを検出できます。

  • OFF
    データベース エンジンは不完全なページを検出できません。

  重要
  構文構造 TORN_PAGE_DETECTION ON | OFF は、将来のバージョンの SQL Server では削除される予定です。新しい開発作業ではこの構文構造の使用を避け、現在この構文構造を使用しているアプリケーションは修正するようにしてください。代わりに、PAGE_VERIFY オプションを使用してください。

• PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
  ディスク I/O パスのエラーが原因で破損したデータベース ページを検出します。ディスク I/O パスのエラーは、データベース破損問題の原因となる可能性があり、一般にはページがディスクに書き込まれている最中に発生した電源障害やディスクのハードウェア障害によって引き起こされます。

  • CHECKSUM
    ページ全体の内容についてチェックサムを計算し、ページがディスクに書き込まれるときに、その値をページ ヘッダーに格納します。ページがディスクから読み取られるときに、チェックサムが再計算され、ページ ヘッダーに格納されているチェックサムの値と比較されます。両方の値が一致しない場合は、SQL Server エラー ログと Windows イベント ログの両方に、エラー メッセージ 824 (チェックサム エラーを示す) がレポートされます。チェックサム エラーは、I/O パスに問題があることを示します。根本的な原因を確認するには、ハードウェア、ファームウェア ドライバー、BIOS、フィルター ドライバー (ウイルス対策ソフトウェアなど)、およびその他の I/O パス コンポーネントを点検する必要があります。

  • TORN_PAGE_DETECTION
    8 KB のデータベース ページに含まれる 512 バイトのセクターごとに特定の 2 ビット パターンを保存し、ページがディスクに書き込まれるときに、データベース ページ ヘッダーに格納します。そのページがディスクから読み取られるときに、ページ ヘッダーに保存されている各セクターの破損ビットと、実際のページ セクター情報とが比較されます。値が一致しない場合は、ページの一部だけがディスクに書き込まれたことを示しています。この場合、SQL Server エラー ログと Windows イベント ログの両方に、エラー メッセージ 824 (破損ページ エラーを示す) がレポートされます。ページの不完全書き込みにより破損したページは、通常はデータベース復旧時に検出されます。ただし、その他の I/O パス障害によっても、破損ページが発生する可能性があります。

  • NONE
    データベース ページの書き込み時に CHECKSUM 値または TORN_PAGE_DETECTION 値は生成されません。SQL Server は、ページ ヘッダーに CHECKSUM 値や TORN_PAGE_DETECTION 値が存在する場合でも、読み取り中にチェックサムや破損ページを確認しません。

  PAGE_VERIFY オプションを使用する場合は、次に示す重要な点を考慮してください。

  • SQL Server 2005 および SQL Server 2008 では、既定値は CHECKSUM です。SQL Server 2000 では、既定値は TORN_PAGE_DETECTION です。

  • ユーザー データベースまたはシステム データベースを SQL Server 2005 または SQL Server 2008 にアップグレードしても、PAGE_VERIFY 値 (NONE または TORN_PAGE_DETECTION) が保持されます。CHECKSUM の使用をお勧めします。 

    注意
    SQL Server の以前のバージョンでは、tempdb データベースの PAGE_VERIFY データベース オプションは NONE に設定されており、変更できません。SQL Server 2008 では、SQL Server の新規インストールに対する tempdb データベースの既定値は CHECKSUM です。インストール済みの SQL Server をアップグレードした場合、既定値は NONE のままです。このオプションは変更できます。tempdb データベースでは CHECKSUM を使用することをお勧めします。

  • TORN_PAGE_DETECTION は、使用するリソースが比較的少なくて済みますが、CHECKSUM による保護の最小限のサブセットしか利用できません。

  • PAGE_VERIFY は、データベースをオフラインにしたり、データベースをロックしたりなど、そのデータベース上での同時実行性を妨げるような措置を取らずに設定できます。

  • CHECKSUM は、TORN_PAGE_DETECTION と共存できません。両方のオプションを同時に有効化することはできません。

  破損ページまたはチェックサム エラーが検出された場合には、データを復元することで復旧できます。障害がインデックス ページだけに限られていれば、インデックスを再構築することで復旧できる可能性があります。チェックサム エラーが発生した場合、影響を受けるデータベース ページの種類を判別するには、DBCC CHECKDB を実行します。復元オプションの詳細については、「RESTORE の引数 (Transact-SQL)」を参照してください。データを復元すれば、データ破損の問題は解決しますが、エラーが継続的に発生することを防ぐには、ディスク ハードウェア障害などの根本的な原因を、直ちに診断して修正しておく必要があります。

  SQL Server は、チェックサム、破損ページ、またはその他の I/O エラーで読み取りに失敗した場合、その読み取りを 4 回再試行します。いずれかの再試行で読み取りに成功した場合には、エラー ログにメッセージが書き込まれ、その読み取りを起動したコマンドは続行されます。再試行が失敗した場合には、そのコマンドはエラー メッセージ 824 で失敗します。

  チェックサム、破損ページ、読み取り再試行、エラー メッセージ 823 と 824、およびその他の SQL Server I/O 監査機能の詳細については、Microsoft Web サイトを参照してください。

  このオプションの現在の設定を確認するには、sys.databases カタログ ビューの page_verify_option 列、または DATABASEPROPERTYEX 関数の IsTornPageDetectionEnabled プロパティを調べます。

<service_broker_option>::=

Service Broker オプション (メッセージ配信の有効化または無効化、新しい Service Broker 識別子の設定、メッセージ交換の優先度の ON または OFF への設定) を制御します。メッセージ配信および Service Broker 識別子の詳細については、「Service Broker の ID の管理」を参照してください。メッセージ交換の優先度の詳細については、「メッセージ交換の優先度」を参照してください。HONOR_BROKER_PRIORITY オプションの使用方法を示す例については、「メッセージ交換の優先度の管理」を参照してください。

• ENABLE_BROKER
  指定したデータベースに対して Service Broker を有効にします。メッセージ配信が開始され、sys.databases カタログ ビューで is_broker_enabled フラグが true に設定されます。データベースは、既存の Service Broker 識別子を保持します。

  注意
  ENABLE_BROKER には排他的データベース ロックが必要です。他のセッションによってデータベースのリソースがロックされている場合、ENABLE_BROKER は、そのセッションがロックを解除するまで待機します。ユーザー データベースで Service Broker を有効にするには、データベースをシングル ユーザー モードに設定するなどして、他のセッションがそのデータベースを使用しないようにしてから ALTER DATABASE SET ENABLE_BROKER ステートメントを実行する必要があります。msdb データベースで Service Broker を有効にするには、まず SQL Server エージェントを停止して、Service Broker が必要なロックを取得できるようにします。

• DISABLE_BROKER
  指定したデータベースに対して Service Broker を無効にします。メッセージ配信が停止され、sys.databases カタログ ビューで is_broker_enabled フラグが false に設定されます。データベースは、既存の Service Broker 識別子を保持します。

• NEW_BROKER
  データベースは新しいブローカー識別子を受信するように指定します。データベースは新しい Service Broker と見なされるため、データベースにおける既存のすべてのメッセージ交換は、終了ダイアログ メッセージを生成せずに、直ちに削除されます。古い Service Broker 識別子を参照するルートは、新しい識別子を使用して作成し直す必要があります。

• ERROR_BROKER_CONVERSATIONS
  Service Broker メッセージ配信を有効にします。 これは、データベースの既存の Service Broker 識別子を保持します。Service Broker は、データベース内のすべてのメッセージ交換をエラーで終了します。これによりアプリケーションは、既存のメッセージ交換に対して、通常のクリーンアップを実行できます。

• HONOR_BROKER_PRIORITY {ON | OFF}

  • ON
    メッセージ交換に割り当てられた優先度が、送信操作で考慮されます。優先度が高いメッセージ交換のメッセージは、低い優先度が割り当てられたメッセージ交換のメッセージよりも先に送信されます。

  • OFF
    すべてのメッセージ交換が既定の優先度レベルを持つと見なして、送信操作が実行されます。

  新しいダイアログや、送信待ちメッセージがないダイアログでは、HONOR_BROKER_PRIORITY オプションに対する変更が直ちに有効になります。ALTER DATABASE の実行時に送信待ちメッセージがあるダイアログでは、それらのメッセージの一部が送信されるまで、新しい設定が反映されません。すべてのダイアログで新しい設定が使用されるようになるまでの時間は、場合により大幅に異なります。

  このプロパティの現在の設定は、sys.databases カタログ ビューの is_broker_priority_honored 列に表示されます。

<snapshot_option>::=

トランザクション分離レベルを決定します。

• ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

  • ON
    スナップショット オプションをデータベース レベルで有効にします。このオプションを有効にすると、トランザクションでスナップショット分離を使用していなくても、DML ステートメントによって行バージョンの生成が開始されます。このオプションを有効にすると、トランザクションで SNAPSHOT トランザクション分離レベルを指定できます。SNAPSHOT 分離レベルでトランザクションが実行されると、すべてのステートメントはトランザクション開始時のデータのスナップショットを参照します。SNAPSHOT 分離レベルで実行されているトランザクションが複数のデータベースのデータにアクセスする場合は、すべてのデータベースで ALLOW_SNAPSHOT_ISOLATION が ON に設定されている必要があります。ALLOW_SNAPSHOT_ISOLATION が OFF になっているデータベース内のテーブルにアクセスする場合は、トランザクション内の各ステートメントで、FROM 句内のすべての参照に対してロック ヒントを使用する必要があります。

  • OFF
    スナップショット オプションをデータベース レベルで無効にします。トランザクションに、SNAPSHOT トランザクション分離レベルを指定できません。

  ALLOW_SNAPSHOT_ISOLATION を新しい状態に (ON から OFF へ、または OFF から ON へ) 設定した場合、ALTER DATABASE は、データベース内にあるすべての既存のトランザクションがコミットされるまで、呼び出し元に制御を返しません。データベースが既に ALTER DATABASE ステートメントで指定した状態にある場合には、制御は呼び出し元に直ちに返されます。ALTER DATABASE ステートメントがすぐに制御を返さない場合には、sys.dm_tran_active_snapshot_database_transactions を使用して、長時間実行されているトランザクションがあるかどうかを確認できます。ALTER DATABASE ステートメントがキャンセルされた場合、データベースは、ALTER DATABASE が開始された時点での状態に留まります。sys.databases カタログ ビューに、データベース内のスナップショット分離トランザクションの状態が表示されます。snapshot_isolation_state_desc = IN_TRANSITION_TO_ON の場合、ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF は 6 秒間待ってから、操作を再試行します。

  データベースが OFFLINE の場合には、ALLOW_SNAPSHOT_ISOLATION の状態を変更できません。

  READ_ONLY のデータベースで ALLOW_SNAPSHOT_ISOLATION を設定すると、このデータベースが後に READ_WRITE に設定された場合でも、この設定が保持されます。

  master、model、msdb、tempdb の各データベースの ALLOW_SNAPSHOT_ISOLATION 設定を変更できます。tempdb でこの設定を変更した場合、データベース エンジン のインスタンスが停止および再起動されるたびに設定が保持されます。model でこの設定を変更すると、その設定は tempdb を除く、新しく作成されたすべてのデータベースの既定値になります。

  masterデータベースと msdb データベースでは、このオプションは既定で ON になります。

  このオプションの現在の設定を確認するには、sys.databases カタログ ビューの snapshot_isolation_state 列を調べます。

• READ_COMMITTED_SNAPSHOT { ON | OFF }

  • ON
    READ COMMITTED スナップショット オプションをデータベース レベルで有効にします。このオプションを有効にすると、トランザクションでスナップショット分離を使用していなくても、DML ステートメントによって行バージョンの生成が開始されます。このオプションを有効にすると、READ COMMITTED 分離レベルを指定しているトランザクションは、ロックではなく、行のバージョン管理を使用します。トランザクションが READ_COMMITTED 分離レベルで実行されている場合、すべてのステートメントは、ステートメントの開始時に存在していたデータのスナップショットを参照します。

  • OFF
    READ COMMITTED スナップショット オプションをデータベース レベルで無効にします。READ COMMITTED 分離レベルを指定しているトランザクションは、ロックを使用します。

  READ_COMMITTED_SNAPSHOT を ON または OFF に設定するには、ALTER DATABASE コマンドを実行している接続以外にデータベースへのアクティブな接続が存在しないようにする必要があります。データベースがシングル ユーザー モードになっている必要はありません。データベースが OFFLINE の場合には、このオプションの状態は変更できません。

  READ_ONLY のデータベースで READ_COMMITTED_SNAPSHOT を設定すると、このデータベースが後で READ_WRITE に設定された場合でも、この設定が保持されます。

  master、tempdb、または msdb システム データベースでは、READ_COMMITTED_SNAPSHOT を ON にすることはできません。model でこの設定を変更すると、変更後の設定は、tempdb を除く新しく作成されたすべてのデータベースの既定値になります。

  このオプションの現在の設定を確認するには、sys.databases カタログ ビューの is_read_committed_snapshot_on 列を調べます。

<sql_option>::=

ANSI 準拠のオプションをデータベース レベルで制御します。

• ANSI_NULL_DEFAULT { ON | OFF }
  CREATE TABLE ステートメントまたは ALTER TABLE ステートメントで NULL を許可するかどうかが明示的に定義されていない場合に、列、別名データ型、または CLR ユーザー定義型の既定値を NULL と NOT NULL のどちらにするかを指定します。制約によって定義された列は、この設定に関係なく制約のルールに従います。

  • ON
    既定値は NULL になります。

  • OFF
    既定値は NOT NULL になります。

  SET ステートメントを使用した接続レベルの設定は、ANSI_NULL_DEFAULT に関するデータベースレベルの既定の設定よりも優先されます。既定では、ODBC クライアントと OLE DB クライアントは、SQL Server のインスタンスに接続するときに、セッションの ANSI_NULL_DEFAULT を ON に設定する接続レベルの SET ステートメントを実行します。詳細については、「SET ANSI_NULL_DFLT_ON (Transact-SQL)」を参照してください。

  ANSI 互換性を確保するために、データベース オプション ANSI_NULL_DEFAULT を ON に設定すると、データベースの既定値が NULL に変更されます。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_ansi_null_default_on 列、または DATABASEPROPERTYEX 関数の IsAnsiNullDefault プロパティを調べます。

• ANSI_NULLS { ON | OFF }

  • ON
    NULL 値との比較結果は、すべて UNKNOWN になります。

  • OFF
    UNICODE 以外の値と NULL 値の比較結果は、両方の値が NULL 値である場合には TRUE になります。

  重要
  今後のバージョンの SQL Server では、ANSI_NULLS が常に ON になり、このオプションを明示的に OFF に設定するすべてのアプリケーションでエラーが発生します。新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。

  SET ステートメントを使用した接続レベルの設定は、ANSI_NULLS の既定のデータベース設定よりも優先されます。既定では、ODBC クライアントと OLE DB クライアントは、SQL Server のインスタンスに接続するときに、セッションの ANSI_NULLS を ON に設定する接続レベルの SET ステートメントを実行します。詳細については、「SET ANSI_NULLS (Transact-SQL)」を参照してください。

  SET ANSI_NULLS は、計算列やインデックス付きビューのインデックスを作成または変更する場合にも、ON に設定する必要があります。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_ansi_nulls_on 列、または DATABASEPROPERTYEX 関数の IsAnsiNullsEnabled プロパティを調べます。

• ANSI_PADDING { ON | OFF }

  • ON
    文字列は、varchar または nvarchar データ型に変換または挿入される前に、同じ長さになります。

    varchar 型または nvarchar 型の列に挿入された文字値の後続の空白も varbinary 型の列に挿入されたバイナリ値の後続の 0 も、切り捨てられません。列の長さに合わせるためにパディングされることはありません。

  • OFF
    varchar 型または nvarchar 型の後続の空白、および varbinary 型の後続の 0 は、切り捨てられます。

  OFF を指定した場合、この設定は新しい列の定義にのみ影響します。

  重要
  今後のバージョンの SQL Server では、ANSI_PADDING が常に ON になり、このオプションを明示的に OFF に設定するすべてのアプリケーションでエラーが発生します。新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正するようにしてください。

  char(n) 列と binary(n) 列が NULL を許容する場合は、ANSI_PADDING を ON に設定すると、列の長さに合うようにパディングが行われますが、ANSI_PADDING を OFF に設定すると、末尾の空白および 0 は切り捨てられます。char(n) 列と binary(n) 列が NULL を許容しない場合は、常に列の長さに合うようにパディングが行われます。

  SET ステートメントを使用した接続レベルの設定は、ANSI_PADDING に関するデータベースレベルの既定の設定よりも優先されます。既定では、ODBC クライアントと OLE DB クライアントは、SQL Server のインスタンスに接続するときに、そのセッションの ANSI_PADDING を ON に設定する接続レベルの SET ステートメントを実行します。詳細については、「SET ANSI_PADDING (Transact-SQL)」を参照してください。

  重要
  ANSI_PADDING は常に ON に設定することをお勧めします。計算列やインデックス付きビューのインデックスを作成または操作するときには、ANSI_PADDING を ON に設定する必要があります。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_ansi_padding_on 列、または DATABASEPROPERTYEX 関数の IsAnsiPaddingEnabled プロパティを調べます。

• ANSI_WARNINGS { ON | OFF }

  • ON
    集計関数で、0 除算などの条件が発生したり、NULL 値が出現した場合に、エラーまたは警告が発行されます。

  • OFF
    0 除算などの条件が発生しても、警告は発行されず、NULL 値が返されます。

  SET ANSI_WARNINGS は、計算列やインデックス付きビューのインデックスを作成または変更する場合には、ON に設定する必要があります。

  SET ステートメントを使用した接続レベルの設定は、ANSI_WARNINGS の既定のデータベース設定よりも優先されます。既定では、ODBC クライアントと OLE DB クライアントは、SQL Server のインスタンスに接続するときに、セッションの ANSI_WARNINGS を ON に設定する接続レベルの SET ステートメントを実行します。詳細については、「SET ANSI_WARNINGS (Transact-SQL)」を参照してください。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_ansi_warnings_on 列、または DATABASEPROPERTYEX 関数の IsAnsiWarningsEnabled プロパティを調べます。

• ARITHABORT { ON | OFF }

  • ON
    クエリ実行中にオーバーフローまたは 0 除算エラーが発生したとき、クエリは終了します。

  • OFF
    これらのエラーが発生すると、警告メッセージが表示されますが、クエリ、バッチ、トランザクションは、エラーが発生しなかったかのように処理を続行します。

  SET ARITHABORT は、計算列やインデックス付きビューのインデックスを作成または変更する場合には、ON に設定する必要があります。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_arithabort_on 列、または DATABASEPROPERTYEX 関数の IsArithmeticAbortEnabled プロパティを調べます。

• COMPATIBILITY_LEVEL {80 | 90 | 100}
  詳細については、「ALTER DATABASE 互換性レベル (Transact-SQL)」を参照してください。

• CONCAT_NULL_YIELDS_NULL { ON | OFF }

  • ON
    オペランドのいずれかが NULL の場合、連結操作の結果は NULL になります。たとえば、文字列 "This is" と NULL を連結すると、結果は "This is" ではなく NULL になります。

  • OFF
    NULL 値は、空の文字列として扱われます。

  CONCAT_NULL_YIELDS_NULL は、計算列やインデックス付きビューのインデックスを作成または変更する場合には、ON に設定する必要があります。

  重要
  今後のバージョンの SQL Server では、CONCAT_NULL_YIELDS_NULL が常に ON になり、このオプションを明示的に OFF に設定するすべてのアプリケーションでエラーが発生します。新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。

  SET ステートメントを使用した接続レベルの設定は、CONCAT_NULL_YIELDS_NULL の既定のデータベース設定よりも優先されます。既定では、ODBC クライアントと OLE DB クライアントは、SQL Server のインスタンスに接続するときに、セッションの CONCAT_NULL_YIELDS_NULL を ON に設定する接続レベルの SET ステートメントを実行します。詳細については、「SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)」を参照してください。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_concat_null_yields_null_on 列、または DATABASEPROPERTYEX 関数の IsNullConcat プロパティを調べます。

• QUOTED_IDENTIFIER { ON | OFF }

  • ON
    二重引用符は識別子を区切るために使用できます。

    二重引用符で囲まれた文字列はすべて、オブジェクト識別子として解釈されます。引用符で囲まれた識別子は、Transact-SQL の識別子の規則に従う必要はありません。引用符で囲まれた識別子はキーワードにすることができ、通常は Transact-SQL 識別子として使用できない文字を含めることもできます。単一引用符 (') がリテラル文字列の一部になっている場合は、それを二重引用符 (") で表記できます。

  • OFF
    識別子は、引用符で囲むことはできず、Transact-SQL の識別子に関するすべての規則に従う必要があります。リテラルは単一引用符と二重引用符のどちらで区切ることもできます。

  SQL Server では識別子を角かっこ ([ ]) で囲むこともできます。角かっこで囲まれた識別子は、QUOTED_IDENTIFIER オプションの設定に関係なくいつでも使用できます。詳細については、「区切られた識別子 (データベース エンジン)」を参照してください。

  テーブルを作成するときに QUOTED IDENTIFIER オプションが OFF に設定されている場合でも、作成されるテーブルのメタデータでは、このオプションは常に ON として格納されます。

  SET ステートメントを使用した接続レベルの設定は、QUOTED_IDENTIFIER の既定のデータベース設定よりも優先されます。既定では、ODBC クライアントと OLE DB クライアントは、SQL Server のインスタンスに接続するときに、QUOTED_IDENTIFIER を ON に設定する接続レベルの SET ステートメントを実行します。詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」を参照してください。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_quoted_identifier_on 列、または DATABASEPROPERTYEX 関数の IsQuotedIdentifiersEnabled プロパティを調べます。

• NUMERIC_ROUNDABORT { ON | OFF }

  • ON
    式の精度が低下した場合にエラーが生成されます。

  • OFF
    有効桁数が失われてもエラー メッセージが生成されず、結果を格納する列または変数の有効桁数に丸められます。

  NUMERIC_ROUNDABORT は、計算列やインデックス付きビューのインデックスを作成または変更する場合は、OFF に設定する必要があります。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_numeric_roundabort_on 列、または DATABASEPROPERTYEX 関数の IsNumericRoundAbortEnabled プロパティを調べます。

• RECURSIVE_TRIGGERS { ON | OFF }

  • ON
    AFTER トリガーの再帰呼び出しを可能にします。

  • OFF
    AFTER トリガーの直接再帰呼び出しだけが実行できなくなります。AFTER トリガーの間接再帰呼び出しも無効にするには、sp_configure を使用して、nested triggers サーバー オプションを 0 に設定します。

  注意
  RECURSIVE_TRIGGERS が OFF の場合は、直接再帰呼び出しのみが回避されます。間接再帰を無効にするには、nested triggers サーバー オプションも 0 に設定する必要があります。

  このオプションの状態を確認するには、sys.databases カタログ ビューの is_recursive_triggers_on 列、または DATABASEPROPERTYEX 関数の IsRecursiveTriggersEnabled プロパティを調べます。

WITH <termination>::=

データベースの状態が変更されるときに、未完了のトランザクションをいつロールバックするかを指定します。データベースがロックされている場合に終了句を省略すると、ALTER DATABASE ステートメントが無限に待機します。指定できる終了句は 1 つだけで、SET 句の後に指定します。

注意
すべてのデータベース オプションで WITH <termination> 句が使用できるわけではありません。詳細については、「解説」の「オプションの設定」にある表を参照してください。

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
  指定した秒数の後、または直ちにロールバックするかどうかを指定します。

• NO_WAIT
  要求されたデータベースの状態またはオプションの変更をすぐに完了できない場合、トランザクションが独自にコミットまたはロールバックするのを待機せずに、要求が失敗します。

説明

オプションの設定

データベース オプションの現在の設定を取得するには、sys.databases カタログ ビューまたは DATABASEPROPERTYEX を使用します。データベースが最初に作成されたときに割り当てられる既定値の一覧については、「データベース オプションの設定」を参照してください。

データベース オプションを設定すると、変更は直ちに有効になります。

新しく作成するデータベースのデータベース オプションの既定値を変更するには、model データベース内の適切なデータベース オプションを変更します。

データベース オプションの中には、WITH <termination> 句を使用しないものや、他のオプションと組み合わせて指定できないものもあります。次の表では、そのようなオプションについて説明します。

オプションのカテゴリ	他のオプションとの組み合わせの可否	WITH <termination> 句の使用の可否
<db_state_option>	可	可
<db_user_access_option>	可	可
<db_update_option>	可	可
<external_access_option>	可	不可
<cursor_option>	可	不可
<auto_option>	可	不可
<sql_option>	可	不可
<recovery_option>	可	不可
<database_mirroring_option>	不可	不可
ALLOW_SNAPSHOT_ISOLATION	不可	不可
READ_COMMITTED_SNAPSHOT	不可	可
<service_broker_option>	可	不可
DATE_CORRELATION_OPTIMIZATION	可	可
<parameterization_option>	可	可
<change_tracking_option>	可	可
<db_encryption>	可	不可

SQL Server のインスタンスのプラン キャッシュは、次のいずれかのオプションを設定することにより消去されます。

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY

プラン キャッシュが消去されると、後続のすべての実行プランが再コンパイルされ、場合によっては、クエリ パフォーマンスが一時的に急激に低下します。プラン キャッシュ内のキャッシュストアが消去されるたびに、「SQL Server は、何らかのデータベース メンテナンス操作または再構成操作の結果発生した '%s' キャッシュストア (プラン キャッシュの一部) のキャッシュストア フラッシュを %d 回検出しました。」という情報メッセージが SQL Server エラー ログに記録されます。このメッセージは、5 分間隔でログに記録されるようになっているので、その期間内に発生したキャッシュのフラッシュが記録されます。 

例

A. データベースのオプションを設定する

次の例は、AdventureWorks サンプル データベースに対して、復旧モデルおよびデータ ページ検証のオプションを設定します。

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

B. データベースを READ_ONLY に設定する

データベースまたはファイル グループの状態を READ_ONLY または READ_WRITE に変更するには、データベースに対する排他的アクセスが必要です。次の例では、排他的アクセスを取得するために、データベースを SINGLE_USER モードに設定します。次に、AdventureWorks データベースの状態を READ_ONLY に設定し、データベースへのアクセス権をすべてのユーザーに戻します。

注意
この例では、最初の ALTER DATABASE ステートメントで、終了オプション WITH ROLLBACK IMMEDIATE を使用しています。すべての未完了のトランザクションはロールバックされ、AdventureWorks サンプル データベースへの他のすべての接続は直ちに接続解除されます。

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

C. データベースの SNAPSHOT 分離を有効にする

次の例では、AdventureWorks データベースに対する SNAPSHOT 分離フレームワーク オプションを有効にします。

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

結果セットは、SNAPSHOT 分離フレームワークが有効であることを示しています。

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

D. 変更の追跡を有効化、変更、および無効化する

次の例では、AdventureWorks データベースで変更の追跡を有効にし、保有期間を 4 日に設定します。

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

次の例では、保有期間を 3 日に変更する方法を示します。

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

次の例では、AdventureWorks データベースで変更の追跡を無効にする方法を示します。

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = OFF;

ドキュメントの変更履歴

変更内容
より正確な内容にするために、AUTO_CREATE_STATISTICS、AUTO_UPDATE_STATISTICS、および AUTO_UPDATE_STATISTICS_ASYNC の説明を修正しました。

関連項目

参照

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

sp_configure (Transact-SQL)

sys.databases (Transact-SQL)

sys.data_spaces (Transact-SQL)

概念

クエリのパフォーマンスを向上させるための統計の使用

変更の追跡の構成および管理

行のバージョン管理に基づく分離レベルの有効化