ALTER DATABASE (Transact-SQL)

  • [アーティクル]

更新 : 2006 年 12 月 12 日

データベース、またはそのデータベースに関連付けられているファイルおよびファイル グループを変更します。データベースに対するファイルやファイル グループの追加と削除、データベースおよびデータベースのファイルやファイル グループの属性の変更、データベースの照合順序の変更、データベース オプションの設定を行えます。データベース スナップショットは変更できません。レプリケーションに関連するデータベース オプションを変更するには、sp_replicationdboption を使用してください。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

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
}

引数

  • database_name
    変更するデータベースの名前を指定します。
  • MODIFY NAME **=**new_database_name
    データベースの名前を、new_database_name で指定した名前に変更します。

  • COLLATE collation_name
    データベースの照合順序を指定します。collation_name には、Windows 照合順序名または SQL 照合順序名のいずれかを指定できます。指定しない場合は、データベースに SQL Server インスタンスの照合順序が割り当てられます。

    Windows 照合順序名および SQL 照合順序名の詳細については、「COLLATE (Transact-SQL)」を参照してください。

<add_or_modify_files>::=

追加、削除、または変更するファイルを指定します。

  • ADD FILE
    データベースにファイルを追加します。

    • TO FILEGROUP { filegroup_name }
      指定されたファイルを追加するファイル グループを指定します。現在のファイル グループ、および現在の既定のファイル グループを表示するには、sys.filegroups カタログ ビューを使用してください。
  • ADD LOG FILE
    指定されたデータベースにログ ファイルを追加します。

  • REMOVE FILE logical_file_name
    SQL Server インスタンスから論理ファイルの説明を削除し、物理ファイルを削除します。ファイルが空でない場合は削除できません。

    • logical_file_name
      ファイルを参照するときに SQL Server で使用される論理名を指定します。

  • MODIFY FILE
    変更するファイルを指定します。一度に 1 つの <filespec> プロパティだけを変更できます。変更するファイルを識別するには、<filespec> に NAME を指定する必要があります。SIZE を指定する場合、ファイルの現在のサイズより新しいサイズの方が大きくなければなりません。

    データ ファイルまたはログ ファイルの論理名を変更するには、変更するファイルの論理名を NAME 句で指定し、NEWNAME 句にそのファイルの新しい論理名を指定します。次に例を示します。

    MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

    データ ファイルまたはログ ファイルを別の場所に移動するには、NAME 句にファイルの現在の論理名を指定し、FILENAME 句に新しいパスとオペレーティング システム ファイル名を指定します。次に例を示します。

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

    フルテキスト カタログを移動する場合は、FILENAME 句に新しいパスだけを指定します。オペレーティングシステム ファイル名は指定しないでください。

    詳細については、「データベース ファイルの移動」を参照してください。

<filespec>::=

ファイル プロパティを制御します。

  • NAME logical_file_name
    ファイルの論理名を指定します。

    • logical_file_name
      ファイルを参照するときに SQL Server インスタンスで使用される論理名を指定します。

  • NEWNAME new_logical_file_name
    ファイルの新しい論理名を指定します。

    • new_logical_file_name
      既存の論理ファイル名と置換する新しい名前を指定します。論理ファイル名は、データベース内で一意であり、識別子の規則に従っている必要があります。論理ファイル名は、英数字、Unicode 定数、通常の識別子、または区切り記号識別子であることが必要です。詳細については、「オブジェクト名としての識別子の使用」を参照してください。

  • FILENAME 'os_file_name'
    オペレーティング システムの (物理) ファイル名を指定します。

    • ' os_file_name '
      ファイルを作成するときにオペレーティング システムで使用されるパスとファイル名を指定します。ファイルは、SQL Server がインストールされているサーバー上に存在する必要があります。ALTER DATABASE ステートメントを実行するには、指定したパスが実際に存在するパスであることが必要です。

      ファイルに対して UNC パスが指定されている場合、SIZE、MAXSIZE、および FILEGROWTH パラメータは設定できません。

      データ ファイルが読み取り専用のセカンダリ ファイルであるか、データベースが読み取り専用である場合を除き、データ ファイルを圧縮ファイル システム上には置かないでください。ログ ファイルは、圧縮ファイル システム上に置くことはできません。詳細については、「読み取り専用のファイル グループと圧縮」を参照してください。

      ファイルが未処理のパーティション上にある場合、os_file_name には、未処理になっている既存のパーティションのドライブ文字のみを指定する必要があります。未処理の各パーティションに配置できるファイルは、それぞれ 1 ファイルだけです。

  • SIZE size
    ファイルのサイズを指定します。

    • size
      ファイルのサイズです。

      ADD FILE と共に指定する場合、size はファイルの初期サイズになります。MODIFY FILE と共に指定する場合、size はファイルの新しいサイズになります。この値には、ファイルの現在のサイズより大きい値を指定する必要があります。

      プライマリ ファイルに size が指定されていない場合、SQL Server 2005 データベース エンジンでは、model データベースのプライマリ ファイルのサイズを使用します。セカンダリ データ ファイルまたはログ ファイルが指定されているにもかかわらず、そのファイルに対して size が指定されていない場合、データベース エンジンでは、そのファイルのサイズが 1 MB になります。

      KB、MB、GB、および TB の各サフィックスを使用して、キロバイト、メガバイト、ギガバイト、またはテラバイトを指定できます。既定値は MB です。整数を指定します。小数は含めないでください。小数部を持つメガバイトの値を指定するには、その値に 1024 を乗算することによって、キロバイトの単位に変換します。たとえば、1.5 MB ではなく 1536 KB と指定します (1.5 × 1024 = 1536)。

  • MAXSIZE { max_size| UNLIMITED }
    ファイルのサイズを拡張する場合の最大サイズを指定します。

    • max_size
      ファイルの最大サイズを指定します。KB、MB、GB、および TB の各サフィックスを使用して、キロバイト、メガバイト、ギガバイト、またはテラバイトを指定できます。既定値は MB です。整数を指定します。小数は含めないでください。max_size を指定しない場合、ファイル サイズはディスクがいっぱいになるまで拡張されます。
    • UNLIMITED
      ディスクがいっぱいになるまでファイルを拡張するように指定します。SQL Server 2005 では、無制限に拡張するように指定した場合、ログ ファイルの最大サイズは 2 TB で、データ ファイルの最大サイズは 16 TB になります。

  • FILEGROWTH growth_increment
    ファイルを自動拡張するときの増分量を指定します。ファイルの FILEGROWTH の設定を MAXSIZE の設定より大きくすることはできません。

    • growth_increment
      新しい領域が必要とされるたびにファイルに追加される領域の容量を指定します。

      値は MB、KB、GB、TB または % の単位で指定できます。サフィックス MB、KB、または % を付けないで数値を指定した場合の既定値は MB です。% を指定すると、1 回の増加量は、増加時のファイル サイズに指定されたパーセンテージを掛けた値になります。指定されたサイズは、最も近い 64 KB の倍数値に丸められます。

      0 は、自動拡張がオフで、領域を追加できないことを示します。

      FILEGROWTH が指定されていない場合、既定値は、データ ファイルが 1 MB、ログ ファイルが 10% で、最小値は 64 KB になります。

      ms174269.note(ja-jp,SQL.90).gifメモ :
      SQL Server 2005 では、データ ファイルの既定の拡張増加量が、10% から 1 MB に変更されました。ログ ファイルの既定値は 10% のままで、変更ありません。

  • OFFLINE
    ファイルをオフラインに設定し、ファイル グループ内のすべてのオブジェクトをアクセス不可にします。

    ms174269.Caution(ja-jp,SQL.90).gif注意 :
    このオプションは、ファイルは破損しているが、復元可能な場合にのみ使用してください。OFFLINE に設定したファイルは、そのファイルをバックアップから復元することによってのみ、オンラインに設定されます。単一ファイルの復元方法の詳細については、「RESTORE (Transact-SQL)」を参照してください。
<add_or_modify_filegroups>::=

データベースに対してファイル グループの追加、変更、または削除を行います。

  • ADD FILEGROUP filegroup_name
    データベースにファイル グループを追加します。
  • REMOVE FILEGROUP filegroup_name
    データベースからファイル グループを削除します。ファイル グループが空でない場合は削除できません。最初に、ファイル グループからすべてのファイルを削除してください。詳細については、前の「REMOVE FILE logical_file_name」を参照してください。

  • MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
    ファイル グループに変更を加えます。ここでは、状態を READ_ONLY または READ_WRITE に設定したり、ファイル グループをデータベースの既定のファイル グループに指定したり、ファイル グループ名を変更することができます。

    • <filegroup_updatability_option>
      ファイル グループに読み取り専用、または読み取り/書き込みのプロパティを設定します。
    • DEFAULT
      既定のデータベース ファイル グループを filegroup_name に変更します。データベース内の 1 つのファイル グループだけを、既定のファイル グループにすることができます。詳細については、「ファイルおよびファイル グループについて」を参照してください。
    • NAME = new_filegroup_name
      ファイル グループ名を new_filegroup_name に変更します。
<filegroup_updatability_option>::=

ファイル グループに読み取り専用、または読み取り/書き込みのプロパティを設定します。

  • READ_ONLY | READONLY
    ファイル グループが読み取り専用であることを指定します。この中のオブジェクトを更新することはできません。プライマリ ファイル グループを読み取り専用にすることはできません。この状態を変更するには、データベースに対する排他的アクセスが必要になります。詳細については、SINGLE_USER 句を参照してください。

    読み取り専用データベースのデータを変更することはできないため、次のようになります。

    • システム起動時に自動復旧がスキップされます。
    • データベースの縮小が不可能になります。
    • 読み取り専用データベースでは、ロックは発生しません。これにより、クエリのパフォーマンスが向上することがあります。
    ms174269.note(ja-jp,SQL.90).gifメモ :
    キーワード READONLY は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では READONLY の使用は避け、現在 READONLY を使用しているアプリケーションは修正するようにしてください。代わりに、READ_ONLY を使用してください。

  • READ_WRITE | READWRITE
    ファイル グループを READ_WRITE に指定します。ファイル グループ内のオブジェクトを更新できます。この状態を変更するには、データベースに対する排他的アクセスが必要になります。詳細については、SINGLE_USER 句を参照してください。

    ms174269.note(ja-jp,SQL.90).gifメモ :
    キーワード READWRITE は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では READWRITE の使用は避け、現在 READWRITE を使用しているアプリケーションは修正するようにしてください。代わりに、READ_WRITE を使用してください。

これらのオプションの状態を確認するには、sys.databases カタログ ビューの is_read_only 列、または DATABASEPROPERTYEX 関数の Updateability プロパティを調べてください。

<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_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 動的管理ビューにクエリを実行することにより、アクティブな非同期の統計ジョブがあるかどうかを確認します。
    3. アクティブなジョブがある場合、それらのジョブが完了するまで待機するか、KILL STATS JOB を使用して手動でジョブを終了します。
  • RESTRICTED_USER
    RESTRICTED_USER モードでは、db_owner 固定データベース ロールと、dbcreator 固定サーバー ロールおよび sysadmin 固定サーバー ロールのメンバだけが、データベースに接続できます。ただし、接続ユーザー数に制限はありません。データベースに対するすべての接続は、ALTER DATABASE ステートメントの終了句で指定したタイムフレーム内に接続解除されます。データベースが RESTRICTED_USER 状態に移行すると、許可されていないユーザーによる接続の試行は拒否されます。
  • MULTI_USER
    データベースに接続するための適切な権限を持つすべてのユーザーが許可されます。

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

<db_update_option>::=

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

  • READ_ONLY
    ユーザーは、データベースのデータを読み取ることができますが、変更はできません。
  • READ_WRITE
    データベースに対して読み取りおよび書き込み操作を行うことができます。

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

<external_access_option>::=

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

  • DB_CHAINING { ON | OFF }

    • ON
      データベースは、複数データベースの組み合わせ所有権のソース データベースまたは対象データベースになることができます。
    • OFF
      データベースは、複数データベースの組み合わせ所有権に参加できません。
    ms174269.note(ja-jp,SQL.90).gif重要 :
    SQL Server のインスタンスでは、cross db ownership chaining サーバー オプションが 0 (OFF) の場合に、この設定が認識されます。cross db ownership chaining が 1 (ON) の場合は、このオプションの値にかかわらず、すべてのユーザー データベースが複数データベースの組み合わせ所有権に参加できます。このオプションは、sp_configure を使用して設定します。

    このオプションを設定するには、sysadmin 固定サーバー ロールのメンバシップが必要です。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 に設定しないことを強くお勧めします。

    このオプションを設定するには、sysadmin 固定サーバー ロールのメンバシップが必要です。

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

<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 プロパティを調べてください。

<auto_option>::=

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

  • AUTO_CLOSE { ON | OFF }

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

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

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

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

    ms174269.note(ja-jp,SQL.90).gifメモ :
    SQL Server の以前のバージョンでは、AUTO_CLOSE は、データベース エンジンへの接続と切断を繰り返すアプリケーションがデータベースにアクセスした場合に、パフォーマンスを低下させる可能性のある同期プロセスでした。SQL Server 2005 では、AUTO_CLOSE プロセスは非同期であるため、データベースを開いたり閉じたりする操作を繰り返しても、パフォーマンスは低下しません。

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

    ms174269.note(ja-jp,SQL.90).gifメモ :
    AUTO_CLOSE が ON の場合、データベースからデータを取得できないため、sys.databases カタログ ビューの一部の列、および DATABASEPROPERTYEX 関数は、NULL を返します。これを解決するには、USE ステートメントを実行してデータベースを開きます。
    ms174269.note(ja-jp,SQL.90).gifメモ :
    データベースをミラー化するには、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
      最適化のためにクエリで必要とされる統計が不足している場合、その統計がクエリ最適化の際に自動的に構築されます。

      統計が追加されると、SQL Server クエリ オプティマイザはクエリの評価方法をより適切に判断できるので、クエリのパフォーマンスが向上します。統計が使用されない場合、それらの統計はデータベース エンジンによって自動的に削除されます。OFF に設定すると、統計は自動的には作成されません。ただし、手動での作成は可能です。詳細については、「インデックス統計」を参照してください。

    • OFF
      統計は手動で作成する必要があります。

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

    ms174269.note(ja-jp,SQL.90).gifメモ :
    クエリ オプティマイザでは、すべての内部システム テーブルを、AUTO_CREATE_STATISTICS が (実際の設定とは関係なく) ON に設定されているものとして処理します。このようなテーブルには、システム ベース テーブル、XML インデックス、フルテキスト インデックス、Service Broker のキュー テーブル、およびクエリ通知テーブルなどがあります。

  • AUTO_SHRINK { ON | OFF }

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

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

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

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

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

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

  • AUTO_UPDATE_STATISTICS { ON | OFF }

    • ON
      最適化のためにクエリで必要とされる統計が古い場合、その統計をクエリ最適化の際に自動的に更新します。
    • OFF
      統計は手動で更新する必要があります。
    ms174269.note(ja-jp,SQL.90).gifメモ :
    UPDATE STATISTICS ステートメントでは、NORECOMPUTE 句が指定されている場合を除き、対象のテーブルまたはビューで自動統計更新を再度有効にすることができます。
    ms174269.note(ja-jp,SQL.90).gifメモ :
    クエリ オプティマイザでは、すべての内部システム テーブルを、AUTO_UPDATE_STATISTICS が (実際の設定とは関係なく) ON に設定されているものとして処理します。このようなテーブルには、システム ベース テーブル、XML インデックス、フルテキスト インデックス、Service Broker のキュー テーブル、およびクエリ通知テーブルなどがあります。

    詳細については、「インデックス統計」を参照してください。

  • AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

    • ON
      クエリで古い統計の自動更新が開始される場合、そのクエリは、統計が更新されるのを待たずにコンパイルを開始します。後続のクエリは、更新された統計が使用可能になった時点で、その統計を使用します。
    • OFF
      クエリで古い統計の自動更新が開始される場合、そのクエリは、更新された統計をクエリ最適化プランで使用できるようになるまで待機します。

    AUTO_UPDATE_STATISTICS が ON に設定されていなければ、このオプションを 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 になります。

    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 は、切り捨てられません。定義された列の長さに合うように、後続の空白または後続の 0 が埋め込まれることはありません。

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

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

    NULL を許容する char(n) および binary(n) 列の場合は、ANSI_PADDING を ON に設定すると、列の長さに合うように埋め込みが行われますが、ANSI_PADDING を OFF に設定すると、後続の空白および 0 は切り捨てられます。NULL を許容しない char(n) および binary(n) 列は、常に列の長さに合うように埋め込みが行われます。

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

    ms174269.note(ja-jp,SQL.90).gif重要 :
    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 プロパティを調べてください。

  • CONCAT_NULL_YIELDS_NULL { ON | OFF }

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

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

    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 に設定します。
    ms174269.note(ja-jp,SQL.90).gifメモ :
    RECURSIVE_TRIGGERS が OFF の場合は、直接再帰呼び出しのみが回避されます。間接再帰呼び出しを無効化するには、nested triggers サーバー オプションを 0 に設定する必要があります。

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

<recovery_option> ::=

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

  • FULL
    メディア障害が発生した後に、トランザクション ログのバックアップを使用して、完全復旧を行います。データ ファイルが損傷した場合、メディアを復旧することで、コミットされたすべてのトランザクションを復元できます。詳細については、「完全復旧モデルでのバックアップ」を参照してください。
  • BULK_LOGGED
    メディア障害が発生した後に、特定の大規模操作または一括操作について、パフォーマンスが最も良く、ログ領域の使用量が最も小さくなるようにして、復旧を行います。一括ログ記録される操作の種類の詳細については、「最小ログ記録操作」を参照してください。BULK_LOGGED 復旧モデルでは、これらの操作に関するログ記録は最小になります。詳細については、「一括ログ復旧モデルでのバックアップ」を参照してください。

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

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

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

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

  • TORN_PAGE_DETECTION { ON | OFF }

    • ON
      データベース エンジンによって不完全なページを検出できます。
    • OFF
      データベース エンジンは不完全なページを検出できません。
    ms174269.note(ja-jp,SQL.90).gif重要 :
    構文構造 TORN_PAGE_DETECTION ON | OFF は、将来のバージョンの Microsoft 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 バイトのセクタごとに特定のビットを保存し、ページがディスクに書き込まれるときに、データベース ページ ヘッダーに格納します。ページがディスクから読み取られるときに、ページ ヘッダーに格納された破損ビットを、実際のページ セクタ情報と比較します。値が一致しない場合は、ページの一部だけがディスクに書き込まれたことを示しています。この場合、SQL Server エラー ログと Windows イベント ログの両方に、エラー メッセージ 824 (破損ページ エラーを示す) がレポートされます。ページの不完全書き込みにより破損したページは、通常はデータベース復旧時に検出されます。ただし、その他の I/O パス障害によっても、破損ページが発生する可能性があります。
    • NONE
      データベース ページの書き込み時に CHECKSUM 値または TORN_PAGE_DETECTION 値は生成されません。SQL Server は、ページ ヘッダーに CHECKSUM 値や TORN_PAGE_DETECTION 値が存在する場合でも、読み取り中にチェックサムや破損ページを確認しません。

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

    • SQL Server 2005 では、既定値は CHECKSUM です。SQL Server 2000 では、既定値は TORN_PAGE_DETECTION です。
    • ユーザー データベースまたはシステム データベースを SQL Server 2005 にアップグレードする場合、PAGE_VERIFY 値 (NONE または TORN_PAGE_DETECTION) は保持されます。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 プロパティを調べてください。

<database_mirroring_option>::=

データベースのデータベース ミラーリングを制御します。データベース ミラーリング オプションで指定した値は、データベースのコピーと、データベース ミラーリング セッション全体の両方に適用されます。1 つの ALTER DATABASE ステートメント { SET PARTNER <partner_option> | SET WITNESS <witness_option>} に対して、<database_mirroring_option> を 1 回だけ指定できます。

ms174269.note(ja-jp,SQL.90).gif重要 :
SET PARTNER または SET WITNESS コマンドは入力時には正常に完了できますが、後で失敗します。
ms174269.note(ja-jp,SQL.90).gifメモ :
構成はパフォーマンスに影響する場合があるので、データベース ミラーリングの構成はピーク タイム以外の時間に行うことをお勧めします。

データベース ミラーリングの詳細については、「データベース ミラーリング」を参照してください。

  • PARTNER <partner_option>
    データベース ミラーリング セッションのフェールオーバー パートナー、およびそれらの動作を定義する、データベース プロパティを制御します。SET PARTNER オプションには、パートナーのうちのいずれか一方で設定すればよいものと、プリンシパル サーバーとミラー サーバーのいずれか一方に限定されているものがあります。詳細については、後述の各 PARTNER オプションを参照してください。SET PARTNER 句は、それを指定したパートナーには関係なく、データベースの両方のコピーに影響します。

    SET PARTNER ステートメントを実行するには、両方のパートナーのエンドポイントの STATE が、STARTED に設定されている必要があります。また、それぞれのパートナー サーバー インスタンスのデータベース ミラーリング エンドポイントの ROLE は、PARTNER または ALL のいずれかに設定されている必要があります。エンドポイントの指定方法の詳細については、「Windows 認証でミラーリング エンドポイントを作成する方法 (Transact-SQL)」を参照してください。サーバー インスタンスのデータベース ミラーリング エンドポイントのロールおよび状態を確認するには、そのインスタンス上で、次の Transact-SQL ステートメントを使用します。

    SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

    <partner_option> ::=

    ms174269.note(ja-jp,SQL.90).gifメモ :
    1 つの SET PARTNER 句で指定できる <partner_option> は 1 つだけです。

    • 'partner_server'
      SQL Server のインスタンスのサーバー ネットワーク アドレスが、新しいデータベース ミラーリング セッションでフェールオーバー パートナーとして動作することを指定します。各セッションには 2 つのパートナーが必要です。一方はプリンシパル サーバーとして起動し、他方はミラー サーバーとして起動します。これらのパートナーは、別々のコンピュータ上に配置することをお勧めします。

      このオプションは、各パートナーでのセッションごとに 1 回指定します。データベース ミラーリング セッションを開始するには、2 つの ALTER DATABASE database SET PARTNER ='partner_server' ステートメントが必要です。これらのステートメントの順序は非常に重要です。最初に、ミラー サーバーに接続し、プリンシパル サーバー インスタンスを partner_server (SET PARTNER ='principal_server') として指定します。次に、プリンシパル サーバーに接続し、ミラー サーバー インスタンスを partner_server (SET PARTNER ='mirror_server') として指定します。これにより、これら 2 つのパートナー間で、データベース ミラーリング セッションが開始されます。詳細については、「データベース ミラーリングの設定」を参照してください。

      partner_server の値は、サーバー ネットワーク アドレスです。構文は次のとおりです。

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

      説明

      • <system-address> は、システム名、完全修飾ドメイン名、IP アドレスなどの文字列です。対象のコンピュータ システムを一意に識別します。
      • <port> は、パートナー サーバー インスタンスのミラーリング エンドポイントに関連付けられているポート番号です。

      詳細については、「サーバー ネットワーク アドレスの指定 (データベース ミラーリング)」を参照してください。

      SET PARTNER ='partner_server' 句の例を次に示します。

      SET PARTNER = 'TCP://MYSERVER.mydomain.Adventure-Works.com:7777'
      ms174269.note(ja-jp,SQL.90).gif重要 :
      SQL Server Management Studio ではなく ALTER DATABASE ステートメントを使用してセットアップしたセッションでは、トランザクションの安全性が既定の完全な安全性に設定され (SAFETY の値が FULL)、セッションは自動フェールオーバーを伴わない高度な安全モードで実行されます。自動フェールオーバーを使用するにはミラーリング監視サーバーを構成し、高パフォーマンス モードで実行するにはトランザクションの安全性を無効 (SAFETY OFF) にします。

    • FAILOVER
      プリンシパル サーバーをミラー サーバーに手動でフェールオーバーします。FAILOVER は、プリンシパル サーバー上でのみ指定できます。このオプションは、SAFETY が FULL に設定されている (既定) 場合にのみ有効です。

      FAILOVER オプションを指定する場合は、データベース コンテキストとして master が必要です。

      詳細については、「手動フェールオーバー」を参照してください。

    • FORCE_SERVICE_ALLOW_DATA_LOSS
      データベースが非同期状態の場合、または自動フェールオーバーが行われずデータベースが同期状態の場合、プリンシパル サーバーで障害が発生すると、データベース サービスを強制的にミラー データベースにします。

      サービスの強制は、プリンシパル サーバーが停止した場合にのみ行うことを強くお勧めします。そうしない場合、一部のクライアントが、新しいプリンシパル データベースではなく、元のプリンシパル データベースにアクセスし続ける可能性があります。

      FORCE_SERVICE_ALLOW_DATA_LOSS は、ミラー サーバー上でのみ使用可能で、かつ次の条件をすべて満たしている必要があります。

      • プリンシパル サーバーが停止している。
      • WITNESS が OFF に設定されているか、またはミラーリング監視サーバーがミラー サーバーに接続されています。

      サービスの強制は、データベースにサービスを直ちに復元するために一部のデータが失われてもかまわないという場合にのみ行ってください。サービスの強制に代わる方法の詳細については、「非同期データベース ミラーリング (高パフォーマンス モード)」を参照してください。

      サービスを強制すると、セッションが中断され、すべてのデータが一時的に元のプリンシパル データベースに保持されます。元のプリンシパルが稼働し、新しいプリンシパル サーバーと通信できるようになると、データベース管理者はサービスを再開できます。セッションを再開すると、すべての未送信ログ レコードと、それに対応する更新は失われます。

      サービスを強制するリスクの詳細については、「強制的なサービスの起動 (データ損失の可能性あり)」を参照してください。

    • OFF
      データベース ミラーリング セッションを削除し、データベースからミラーリングを削除します。OFF は、どちらのパートナー上でも指定できます。詳細については、「データベース ミラーリングの削除」で、ミラーリングを削除した場合の影響を参照してください。
    • RESUME
      中断状態のデータベース ミラーリング セッションを再開します。RESUME は、プリンシパル サーバー上でのみ指定できます。

    • SAFETY { FULL | OFF }
      トランザクションの安全性のレベルを設定します。SAFETY は、プリンシパル サーバー上でのみ指定できます。

      既定値は FULL です。SAFETY が FULL の場合、データベース ミラーリング セッションは高度な安全モードで同期的に実行されます。OFF の場合は、データベース ミラーリング セッションは高パフォーマンス モードで非同期的に実行されます。

      高度な安全モードの動作は、次のように部分的にミラーリング監視に依存します。

      • SAFETY が FULL に設定され、ミラーリング監視がセッションに対して設定されている場合、セッションは自動フェールオーバーを伴う高度な安全モードで実行されます。データベースが同期され、ミラー サーバー インスタンスとミラーリング監視が引き続き相互接続している場合 (つまりクォーラムを保持している場合)、プリンシパル サーバーが失われると、セッションでは自動的にフェールオーバーが発生します。詳細については、「クォーラム : データベースの可用性にミラーリング監視サーバーが与える影響」を参照してください。
        ミラーリング監視がセッションに対して設定されていても、ミラーリング監視サーバーが切断されていると、ミラー サーバーが利用できなくなるためプリンシパル サーバーがダウンします。
      • SAFETY が FULL に設定され、ミラーリング監視が OFF に設定されている場合、セッションは自動フェールオーバーを伴わない高度な安全モードで実行されます。ミラー サーバー インスタンスがダウンしても、プリンシパル サーバー インスタンスは影響を受けません。プリンシパル サーバー インスタンスがダウンした場合、ミラー サーバー インスタンスにサービスの提供を強制的に移行できます (データが損失する可能性があります)。

      SAFETY が OFF に設定されている場合、セッションは高パフォーマンス モードで実行されます。この場合、自動フェールオーバーも手動フェールオーバーもサポートされません。ただし、ミラー サーバーで発生した問題が、プリンシパル サーバーに影響を及ぼすことはありません。WITNESS が OFF に設定されているか、ミラーリング監視サーバーがミラーに現在接続されているときに、プリンシパル サーバー インスタンスがダウンした場合、必要に応じてミラー サーバー インスタンスにサービスの提供を強制的に移行できます (データが損失する可能性があります)。サービスの強制の詳細については、前の「FORCE_SERVICE_ALLOW_DATA_LOSS」を参照してください。

      ms174269.note(ja-jp,SQL.90).gif重要 :
      高パフォーマンス モードは、ミラーリング監視の使用を想定していません。ただし、SAFETY を OFF に設定した場合は常に、WITNESS も OFF に設定することを強くお勧めします。

      詳細については、「Transact-SQL の設定とデータベース ミラーリングの動作モード」を参照してください。

    • SUSPEND
      データベース ミラーリング セッションを中断します。

      SUSPEND は、どちらのパートナー上でも指定できます。

    • TIMEOUT integer
      タイムアウト時間を秒単位で指定します。タイムアウト時間は、ミラーリング セッションの別のインスタンスからの PING メッセージを受信するために、サーバー インスタンスが待機する最大時間です。この時間を過ぎると、待機していたインスタンスは接続解除されたものと見なされます。

      TIMEOUT オプションは、プリンシパル サーバー上でのみ指定できます。このオプションを指定しない場合、この時間は既定で 10 秒に設定されます。5 以上の値を指定すると、タイムアウト時間は指定した秒数に設定されます。タイムアウト値に 0 ～ 4 秒を指定すると、タイムアウト時間は自動的に 5 秒に設定されます。

      ms174269.note(ja-jp,SQL.90).gif重要 :
      タイムアウト期間を 10 秒以上にしておくことをお勧めします。値を 10 秒未満に設定すると、負荷の高いシステムでは PING を受信できず、誤認エラーが示される可能性があります。

      詳細については、「データベース ミラーリング中に発生する可能性のあるエラー」を参照してください。

  • WITNESS <witness_option>
    データベースのミラーリング監視を定義するデータベース プロパティを制御します。SET WITNESS 句は、データベースの両方のコピーに影響しますが、SET WITNESS はプリンシパル サーバー上でのみ指定できます。ミラーリング監視がセッションに対して設定されている場合にデータベースを使用できるようにするには、SAFETY の設定に関係なく、クォーラムが必要です。詳細については、「クォーラム : データベースの可用性にミラーリング監視サーバーが与える影響」を参照してください。

    ミラーリング監視とフェールオーバー パートナーは、別々のコンピュータに配置することをお勧めします。ミラーリング監視の詳細については、「データベース ミラーリング監視サーバー」を参照してください。自動フェールオーバーの詳細については、「自動フェールオーバー」を参照してください。

    SET WITNESS ステートメントを実行するには、プリンシパル サーバー インスタンスおよびミラーリング監視サーバー インスタンスのエンドポイントの STATE が STARTED に設定されている必要があります。また、ミラーリング監視サーバー インスタンスのデータベース ミラーリング エンドポイントの ROLE は、WITNESS または ALL のいずれかに設定されている必要があります。エンドポイントの指定の詳細については、「データベース ミラーリング エンドポイント」を参照してください。

    サーバー インスタンスのデータベース ミラーリング エンドポイントのロールおよび状態を確認するには、そのインスタンス上で、次の Transact-SQL ステートメントを使用します。

    SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
    ms174269.note(ja-jp,SQL.90).gifメモ :
    データベースのプロパティは、ミラーリング監視では設定できません。

    <witness_option> ::=

    ms174269.note(ja-jp,SQL.90).gifメモ :
    1 つの SET WITNESS 句で指定できる <witness_option> は 1 つだけです。

    • 'witness_server'
      データベース エンジンのインスタンスが、データベース ミラーリング セッションのミラーリング管理サーバーとして動作することを指定します。SET WITNESS ステートメントは、プリンシパル サーバー上でのみ指定できます。

      SET WITNESS ='witness_server' ステートメントでは、witness_server の構文は partner_server の構文と同じです。

    • OFF
      データベース ミラーリング セッションから、ミラーリング監視を削除します。ミラーリング監視を OFF に設定すると、自動フェールオーバーが無効化されます。データベースが FULL SAFETY に設定され、ミラーリング監視が OFF に設定されている場合、ミラー サーバーに障害が発生すると、プリンシパル サーバーはデータベースを使用不可にします。
<service_broker_option>::=

Service Broker のオプションを制御します。

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

    ms174269.note(ja-jp,SQL.90).gifメモ :
    データベースで SQL Server Service Broker を有効にするには、データベースのロックが必要です。msdb データベースで Service Broker を有効にするには、Service Broker で必要なロックを取得できるよう、最初に SQL Server エージェントを停止します。
  • DISABLE_BROKER
    指定したデータベースに対して Service Broker を無効にします。is_broker_enabled フラグは sys.databases カタログ ビューで false に設定され、メッセージ配信は停止されます。
  • NEW_BROKER
    データベースは新しいブローカ識別子を受信します。データベースは新しい Service Broker と見なされるため、データベースにおける既存のすべてのメッセージ交換は、終了ダイアログ メッセージを生成せずに、直ちに削除されます。
  • ERROR_BROKER_CONVERSATIONS
    データベースがアタッチされたときに、データベース内のメッセージ交換はエラー メッセージを受信します。これによりアプリケーションは、既存のメッセージ交換に対して、通常のクリーンアップを実行できます。
<date_correlation_optimization_option> ::=

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

  • DATE_CORRELATION_OPTIMIZATION { ON | OFF }

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

    DATE_CORRELATION_OPTIMIZATION を ON に設定する場合、データベースに対するアクティブな接続が存在してはいけません。ただし、ALTER DATABASE ステートメントが実行されている接続は、存在しても問題はありません。設定した後、複数の接続がサポートされます。

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

<parameterization_option> ::=

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

  • PARAMETERIZATION { SIMPLE | FORCED }

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

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

<snapshot_option>::=

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

  • ALLOW_SNAPSHOT_ISOLATION { ON| OFF }

    • ON
      トランザクションでは、SNAPSHOT トランザクション分離レベルを指定できます。トランザクションが SNAPSHOT 分離レベルで実行されている場合、すべてのステートメントは、トランザクションの開始時に存在しているデータのスナップショットを参照します。SNAPSHOT 分離レベルで実行されているトランザクションが複数のデータベースのデータにアクセスする場合は、すべてのデータベースで ALLOW_SNAPSHOT_ISOLATION を ON に設定するか、またはトランザクション内の各ステートメントで、FROM 句内のすべての参照に対してロック ヒントを使用する必要があります。この FROM 句とは、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 分離レベルを指定しているトランザクションは、ロックではなく行バージョンを使用します。トランザクションが READ_COMMITTED 分離レベルで実行されている場合、すべてのステートメントは、ステートメントの開始時に存在していたデータのスナップショットを参照します。
    • OFF
      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 列を調べてください。

WITH <termination>::=

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

ms174269.note(ja-jp,SQL.90).gifメモ :
すべてのデータベース オプションで WITH <termination> 句が使用できるわけではありません。詳細については、「解説」の「オプションの設定」にある表を参照してください。
  • ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
    指定した秒数の後、または直ちにロールバックするかどうかを指定します。
  • NO_WAIT
    要求されたデータベースの状態またはオプションの変更がすぐに完了しない場合、トランザクションがコミットまたはロールバックするのを待機せずに、要求が失敗します。

解説

データベースを削除するには、DROP DATABASE を使用します。

データベースの名前を変更するには、ALTER DATABASE で、MODIFY NAME = new_database_name オプションを使用します。

データベースのサイズを縮小するには、DBCC SHRINKDATABASE を使用します。

BACKUP ステートメントの実行中にファイルを追加したり削除したりすることはできません。

各データベースに、最大 32,767 のファイルと 32,767 のファイル グループを指定できます。

ALTER DATABASE ステートメントは自動コミット モード (既定のトランザクション管理モード) で実行する必要があり、明示的または暗黙的なトランザクション モードでは許可されません。詳細については、「トランザクションの自動コミット」を参照してください。

SQL Server 2005 では、データベース ファイルの状態 (オンラインかオフラインかなど) は、データベースの状態とは別に保持されます。詳細については、「ファイルの状態」を参照してください。ファイル グループ内のファイルの状態は、ファイル グループ全体の可用性を決定します。ファイル グループが使用可能であるためには、ファイル グループ内のすべてのファイルがオンラインであることが必要です。ファイル グループがオフラインの場合、SQL ステートメントでそのファイル グループにアクセスを試行するとエラーが発生します。SELECT ステートメントのクエリ プランを作成する場合、クエリ オプティマイザは、オフラインのファイル グループにある非クラスタ化インデックスやインデックス付きビューを回避します。これにより、これらのステートメントは正常に実行できます。ただし、オフラインのファイル グループに、対象テーブルのヒープやクラスタ化インデックスが含まれている場合には、SELECT ステートメントは失敗します。また、オフラインのファイル グループ内にある、インデックスを持つテーブルを変更する INSERT、UPDATE、または DELETE ステートメントは失敗します。

データベースが RESTORING 状態にある場合、大半の ALTER DATABASE ステートメントは失敗します。ただし、データベース ミラーリング オプションの設定は例外です。データベースが RESTORING 状態になるのは、アクティブな復元操作中や、バックアップ ファイルの破損によりデータベースまたはログ ファイルの復元操作が失敗した場合などです。詳細については、「バックアップの破損による SQL Server 復元エラーの対応」を参照してください。

オプションの設定

データベース オプションの現在の設定を取得するには、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>

はい

はい

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

OFFLINE

READ_WRITE

ONLINE

MODIFY FILEGROUP DEFAULT

MODIFY_NAME

MODIFY FILEGROUP READ_WRITE

COLLATE

MODIFY FILEGROUP READ_ONLY

READ_ONLY

 

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

ファイルの移動

SQL Server 2005 では、FILENAME に新しい場所を指定することにより、システムまたはユーザー定義のデータ、およびログ ファイルを移動することができます。これは、次のようなシナリオで役立ちます。

  • 障害の復旧。たとえば、データベースがハードウェア障害によって未確認モードに入ったり、シャットダウンした場合です。
  • 予定された再配置。
  • スケジュールされたディスク メンテナンスのための再配置。

詳細については、「データベース ファイルの移動」を参照してください。

ファイルの初期化

既定では、データ ファイルおよびログ ファイルは、次のいずれかの操作を実行したときに、ファイルを 0 で埋め込むことにより初期化されます。

  • データベースの作成
  • 既存データベースへのファイルの追加
  • 既存のファイルのサイズの拡張
  • データベースまたはファイル グループの復元

SQL Server 2005 では、データ ファイルは即時に初期化されます。そのため、このようなファイル操作を高速に実行できます。詳細については、「データベース ファイルの初期化」を参照してください。

データベースの照合順序の変更

データベースに別の照合順序を適用する前に、次の条件が満たされているかどうかを確認してください。

  1. 現在データベースを使用しているのは、1 人だけである。
  2. データベースの照合順序に依存するスキーマ バインド オブジェクトがない。
    データベースの照合順序に依存する次のオブジェクトがデータベース内に存在する場合、ALTER DATABASE database_name COLLATE ステートメントは失敗します。SQL Server は、ALTER アクションをブロックしている各オブジェクトに対してエラー メッセージを返します。
    • SCHEMABINDING を指定して作成されたユーザー定義関数およびビュー
    • 計算列
    • CHECK 制約
    • 既定のデータベース照合順序から継承した照合順序を持つ文字型列がテーブルにある場合に、そのテーブルを返すテーブル値関数
  3. データベースの照合順序の変更によって、データベース オブジェクトのシステム名に重複が発生しない。
    照合順序の変更によって名前が重複する場合、次の名前空間が原因でデータベースの照合順序の変更が失敗することがあります。
    • プロシージャ、テーブル、トリガ、ビューなどのオブジェクト名
    • スキーマ名
    • グループ、ロール、ユーザーなどのプリンシパル
    • システム データ型、ユーザー定義データ型などのスカラ データ型の名前
    • フルテキスト カタログ名
    • オブジェクト内の列名またはパラメータ名
    • テーブル内のインデックス名
      新しい照合順序によって名前が重複すると、変更操作が失敗し、SQL Server は重複が見つかった名前空間を示すエラー メッセージを返します。

データベース情報の表示

カタログ ビュー、システム関数、およびシステム ストアド プロシージャを使用して、データベース、ファイルおよびファイル グループについての情報を返すことができます。詳細については、「データベース メタデータの表示」を参照してください。

権限

データベースに対する ALTER 権限が必要です。

例

A. データベースにファイルを追加する

次の例では、5 MB のデータ ファイルを AdventureWorks データベースに追加します。

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. 2 つのファイルから成るファイル グループをデータベースに追加する

次の例では、AdventureWorks データベースに Test1FG1 ファイル グループを作成し、そのファイル グループに 5 MB のファイルを 2 つ追加します。

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. 2 つのログ ファイルをデータベースに追加する

次の例では、5 MB のログ ファイルを 2 つ、 AdventureWorks データベースに追加します。

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. データベースからファイルを削除する

次の例では、例 B で追加したファイルの一方を削除します。

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

E. ファイルを変更する

次の例では、例 B で追加したファイルのうち、1 つのサイズを拡張します。

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

F. ファイルを新しい場所に移動する

次の例では、例 A で作成した Test1dat2 ファイルを、新しいディレクトリに移動します。

ms174269.note(ja-jp,SQL.90).gifメモ :
この例を実行する前に、ファイルを新しいディレクトリに物理的に移動しておく必要があります。その後、SQL Server のインスタンスをいったん停止してから起動するか、または AdventureWorks データベースをいったん OFFLINE にしてから ONLINE にして、変更を実装します。 
USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. tempdb を新しい場所に移動する

次の例では、tempdb をディスク上の現在の場所から別の場所に移動します。tempdb は MSSQLSERVER サービスが開始されるたびに再作成されるので、データ ファイルとログ ファイルを物理的に移動する必要はありません。これらのファイルは、MSSQLSERVER サービスが手順 3. で再開したときに作成されます。MSSQLSERVER サービスを再開しない限り、tempdb は引き続き元の場所で機能します。

  1. tempdb データベースの論理ファイル名と、ディスク上での現在の場所を確認します。

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

  2. 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. SQL Server のインスタンスをいったん停止してから再起動します。

  4. ファイルの変更を確認します。

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

  5. tempdb.mdf ファイルと templog.ldf ファイルを元の場所から削除します。

H. ファイル グループを既定にする

次の例では、例 B で作成した Test1FG1 ファイル グループを、既定のファイル グループにします。次に、既定のファイル グループを、PRIMARY ファイル グループに再設定します。PRIMARY は、角かっこまたは引用符で区切る必要があります。

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

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

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

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

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

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

ms174269.note(ja-jp,SQL.90).gifメモ :
この例では、最初の 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

K. データベースでの 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

L. ミラーリング監視を使用したデータベース ミラーリング セッションを作成する

ミラーリング監視を使用したデータベース ミラーリングをセットアップするには、セキュリティを構成し、ミラー データベースを準備し、ALTER DATABASE を使用してパートナーを設定する必要があります。完全なセットアップ プロセスの例については、「データベース ミラーリングの設定」を参照してください。

M. データベース ミラーリング セッションを手動でフェールオーバーする

手動フェールオーバーは、どちらのデータベース ミラーリング パートナーからでも開始できます。フェールオーバーする前に、現在プリンシパル サーバーであると思われるサーバーが、実際にプリンシパル サーバーであるかどうかを確認する必要があります。たとえば、AdventureWorks データベースの場合、現在プリンシパル サーバーであると思われるサーバー インスタンスで、次のクエリを実行します。

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

そのサーバー インスタンスが実際にプリンシパルである場合、mirroring_role_desc の値は Principal になります。このサーバー インスタンスがミラー サーバーの場合には、SELECT ステートメントは Mirror を返します。

次の例では、そのサーバーが現在のプリンシパルであることを前提としています。

  1. データベース ミラーリング パートナーに手動でフェールオーバーするには、次のステートメントを実行します。

    ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
GO

  2. 新しいミラーでのフェールオーバーの結果を確認するには、次のクエリを実行します。

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

    mirroring_role_desc の現在の値は、Mirror です。

参照

関連項目

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)

その他の技術情報

行のバージョン管理に基づく分離レベルの有効化
システム データベース

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 12 月 12 日
新しい内容 :
  • プラン キャッシュを消去するオプションに関する情報を「解説」の「オプションの設定」および AUTO_CLOSE の定義に追加。
  • シングル ユーザー モードの AUTO_UPDATE_STATISTICS_ASYNC オプションの使用に関する情報を SINGLE_USER の定義に追加。

2006 年 4 月 14 日
変更内容 :
  • FAILOVER オプションの説明を更新し、データベース コンテキストとして master が必要であることを記載。
  • 「<database_mirroring_option>」の概要に、重要な注記を追加。
  • 引数 ALLOW_SNAPSHOT_ISOLATION の説明で、データベースにおけるスナップショット分離トランザクションの状態の判別に関する情報を更新。およびこのオプションが変更された場合に状態が SQL Server の動作に与える影響に関する情報を更新。
  • DATE_CORRELATION_OPTIMIZATION の定義を修正。

2005 年 12 月 5 日
新しい内容 :
  • ENABLE_BROKER の定義に注記を追加。
  • アップグレードされたデータベースでの PAGE_VERIFY オプションの変更に関して、推奨事項を追加。
変更内容 :
  • SUPPLEMENTAL_LOGGING オプションを削除。
  • 例 G を修正。
  • システム データベースの TRUSTWORTHY オプションに関する情報を修正。
  • READ_COMMITTED_SNAPSHOT の定義を更新し、シングル ユーザー モードが必要でないことを明記。
  • <db_state_option> の定義を更新し、データベースが RESTORING 状態にある場合、OFFLINE、ONLINE、EMERGENCY オプションは設定できないことを明記。