ALTER DATABASE (Transact-SQL) の File および Filegroup オプション

[アーティクル]
05/15/2023

データベースに関連付けられているファイルおよびファイルグループを変更します。データベースに対するファイルやファイルグループの追加と削除、およびデータベースおよびデータベースのファイルやファイルグループの属性の変更を行います。 ALTER DATABASE の他のオプションについては、「ALTER DATABASE」をご覧ください。

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

製品を選択する

次の行で、興味のある製品の名前を選択すると、その製品の情報のみが表示されます。

* SQL Server *

SQL Managed Instance

構文

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}

<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' | 'filestream_path' | 'memory_optimized_data_path' } ]
    [ , 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
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option>
        | DEFAULT
        | NAME = new_filegroup_name
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE }
    | { READ_ONLY | READ_WRITE }
}

引数

<add_or_modify_files>::=

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

database_name: 変更するデータベースの名前です。

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

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

ADD LOG FILE: 指定されたデータベースにログファイルを追加します。

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

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

警告

FILE_SNAPSHOT のあるデータベースファイルを削除する関連付けられているバックアップは成功しますが、関連付けられたスナップショットを参照するデータベースファイルのバックアップを無効化を回避するのには削除されません。ファイルは切り捨てられますが、FILE_SNAPSHOT のバックアップをそのままの状態に保つために物理的には削除されません。詳しくは、「Microsoft Azure Blob Storage を使用した SQL Server のバックアップと復元」をご覧ください。 適用対象: SQL Server (SQL Server 2016 (13.x) 以降)。

変更するファイルを指定します。一度に 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 句に新しいパスだけを指定します。オペレーティングシステムファイル名は指定しないでください。

詳しくは、「データベースファイルの移動」をご覧ください。

FILESTREAM ファイルグループの場合、NAME をオンラインで変更できます。 FILENAME はオンラインで変更できますが、コンテナーを物理的に再配置し、サーバーをシャットダウンして再起動するまで、変更は有効になりません。

FILESTREAM ファイルを OFFLINE に設定できます。 FILESTREAM ファイルをオフラインにすると、その親ファイルグループがオフラインとして内部的にマークされるため、そのファイルグループ内の FILESTREAM データへのアクセスはすべて失敗します。

Note

<add_or_modify_files> オプションは、包含データベースでは使用できません。

<filespec>::=

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

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

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

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

new_logical_file_name: 既存の論理ファイル名と置換する新しい名前を指定します。論理ファイル名は、データベース内で一意であり、識別子の規則に従っている必要があります。この名前は、文字定数、Unicode 定数、標準の識別子、区切られた識別子のいずれでもかまいません。

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

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

Note

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

システムデータベースを UNC 共有ディレクトリに置くことはできません。

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

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

'filestream_path': FILESTREAM ファイルグループの場合、FILENAME は FILESTREAM データが格納されるパスを参照します。最後のフォルダーまでのパスが存在する必要がありますが、最後のフォルダーは存在できません。たとえば、パス C:\MyFiles\MyFilestreamData を指定する場合は、ALTER DATABASE を実行する前に、C:\MyFiles が存在している必要がありますが、MyFilestreamData フォルダーは存在できません。

Note

SIZE プロパティおよび FILEGROWTH プロパティは、FILESTREAM ファイルグループには適用されません。

'memory_optimized_data_path': メモリ最適化ファイルグループでは、FILENAME はメモリ最適化データが格納されるパスを示します。最後のフォルダーまでのパスが存在する必要がありますが、最後のフォルダーは存在できません。たとえば、パス C:\MyFiles\MyData を指定する場合は、ALTER DATABASE を実行する前に、C:\MyFiles が存在している必要がありますが、MyData フォルダーは存在できません。

ファイルグループとファイル (<filespec>) は、同じステートメントで作成する必要があります。

Note

SIZE と FILEGROWTH の各プロパティは、MEMORY_OPTIMIZED_DATA ファイルグループには適用されません。

メモリ最適化ファイルグループの詳細については、「メモリ最適化ファイルグループ」を参照してください。

SIZE size: ファイルのサイズを指定します。 SIZE は、FILESTREAM ファイルグループには適用されません。

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

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

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

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

Note

次の場合、SIZE を設定することはできません。

ファイルに UNC パスが指定されている場合
FILESTREAM および MEMORY_OPTIMIZED_DATA のファイルグループ

MAXSIZE { max_size| UNLIMITED }: ファイルのサイズの上限を指定します。

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

UNLIMITED: ディスクがいっぱいになるまでファイルを拡張するように指定します。 SQL Server では、無制限に拡張するファイル固有のログの最大サイズは 2 TB で、データファイルの最大サイズは 16 TB です。 FILESTREAM コンテナーに対してこのオプションを指定した場合、最大サイズはありません。ディスクがいっぱいになるまでファイルサイズが拡張します。

注意

ファイルに UNC パスが指定されている場合、MAXSIZE は設定できません。

FILEGROWTH growth_increment: ファイルを自動拡張するときの増加量を指定します。ファイルの FILEGROWTH の設定を MAXSIZE の設定より大きくすることはできません。 FILEGROWTH は、FILESTREAM ファイルグループには適用されません。

growth_increment: 新しい領域が必要とされるたびにファイルに追加される領域の容量です。

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

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

FILEGROWTH が指定されていない場合、既定値は次のとおりです。

Version	既定値
SQL Server 2016 (13.x) 以降	データ 64 MB。ログファイル 64 MB。
SQL Server 2005 (9.x) 以降	データ 1 MB。ログファイル 10%。
SQL Server 2005 (9.x) の前	データ 10%。ログファイル 10%。

注意

次の場合、FILEGROWTH を設定することはできません。

ファイルに UNC パスが指定されている場合
FILESTREAM および MEMORY_OPTIMIZED_DATA のファイルグループ

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

注意事項

このオプションは、ファイルは破損しているが、復元可能な場合にのみ使用してください。 OFFLINE に設定したファイルは、そのファイルをバックアップから復元することによってのみ、オンラインに設定されます。単一ファイルの復元の詳細については、「RESTORE」を参照してください。

<filespec> オプションは、包含データベースでは使用できません。

<add_or_modify_filegroups>::=

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

ADD FILEGROUP filegroup_name: データベースにファイルグループを追加します。

CONTAINS FILESTREAM: ファイルグループで FILESTREAM バイナリラージオブジェクト (BLOB) をファイルシステムに格納することを指定します。

CONTAINS MEMORY_OPTIMIZED_DATA

適用対象: SQL Server (SQL Server 2014 (12.x) 以降)

ファイルグループでメモリ最適化データをファイルシステムに格納することを指定します。詳細については、「インメモリ OLTP - インメモリ最適化」を参照してください。データベースあたり 1 つの MEMORY_OPTIMIZED_DATA ファイルグループのみが許可されます。メモリ最適化テーブルを作成する場合は、ファイルグループを空にすることはできません。ファイルが少なくとも 1 つ必要です。 filegroup_name パスを参照します。最後のフォルダーまでのパスが存在する必要がありますが、最後のフォルダーは存在できません。

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

Note

FILESTREAM ガベージコレクターによって FILESTREAM コンテナーからすべてのファイルが削除されない限り、ALTER DATABASE REMOVE FILE 操作で FILESTREAM コンテナーを削除する試みは失敗し、エラーが返されます。このトピックの「FILESTREAM コンテナーの削除」セクションを参照してください。

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

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

DEFAULT: 既定のデータベースファイルグループを filegroup_name に変更します。データベース内の 1 つのファイルグループだけを、既定のファイルグループにすることができます。詳細については、「 Database Files and Filegroups」を参照してください。

NAME = new_filegroup_name: ファイルグループ名を new_filegroup_name に変更します。

AUTOGROW_SINGLE_FILE 適用対象: SQL Server (SQL Server 2016 (13.x) 以降)

ファイルグループ内のファイルが自動拡張のしきい値を満たす場合は、そのファイルのみが拡張されます。これは既定値です。

AUTOGROW_ALL_FILES

適用対象: SQL Server (SQL Server 2016 (13.x) 以降)

ファイルグループ内のファイルが自動拡張のしきい値を満たすときに、ファイルグループ内のすべてのファイルを拡張します。

Note

これは TempDB の既定値です。

<filegroup_updatability_option>::=

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

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

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

システム起動時に自動復旧がスキップされます。
データベースの圧縮が不可能になります。
読み取り専用データベースでは、ロックは発生しません。これにより、クエリのパフォーマンスが向上することがあります。

Note

キーワード READONLY は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、READONLY の使用は避け、現在 READONLY を使用しているアプリケーションは修正するようにしてください。代わりに READ_ONLY を使用してください

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

Note

キーワード READWRITE は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、READWRITE の使用は避け、現在 READWRITE を使用しているアプリケーションは READ_WRITE を使用するように修正することを計画してください。

ヒント

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

解説

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

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

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

SQL Server 2005 (9.x) 以降では、データベースファイルの状態 (オンラインかオフラインかなど) は、データベースの状態とは別に保持されます。詳しくは、「ファイルの状態」をご覧ください。

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

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

メモリ最適化ファイルグループには、SIZE および FILEGROWTH パラメーターを設定することはできません。

キーワード READONLY は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、READONLY の使用は避け、現在 READONLY を使用しているアプリケーションは修正するようにしてください。代わりに READ_ONLY を使用してください

ファイルの移動

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

障害復旧。たとえば、ハードウェア障害により、データベースが問題のあるモードになる場合やシャットダウンされるた場合など。
計画に従った再配置。
スケジュールされたディスクメンテナンスとしての再配置。

詳しくは、「データベースファイルの移動」をご覧ください。

ファイルの初期化

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

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

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

FILESTREAM コンテナーの削除

"DBCC SHRINKFILE" 操作によって FILESTREAM コンテナーが空にされた場合でも、データベースは、さまざまなシステム保守上の理由から、削除されたファイルへの参照を維持する必要があります。 sp_filestream_force_garbage_collection を使用すると、FILESTREAM ガベージコレクターが実行され、これらのファイルを削除しても安全な場合は削除されます。 FILESTREAM ガベージコレクターによって FILESTREAM コンテナーからすべてのファイルが削除されない限り、ALTER DATABASE REMOVE FILE 操作で FILESTREAM コンテナーを削除する試みは失敗し、エラーが返されます。 FILESTREAM コンテナーを削除する場合は、次の手順をお勧めします。

EMPTYFILE オプションを指定して DBCC SHRINKFILE を実行し、このコンテナーのアクティブなコンテンツを他のコンテナーに移動します。
ログバックアップが完全または一括ログ復旧モデルで作成されたことを確認します。
レプリケーションログリーダージョブが実行されていることを確認します (該当する場合)。
sp_filestream_force_garbage_collection を実行して、このコンテナー内の不要になったファイルをガベージコレクターで強制的に削除します。
REMOVE FILE オプションを指定して ALTER DATABASE を実行し、このコンテナーを削除します。
もう一度手順 2. ～ 4. を繰り返して、ガベージコレクションを完了します。
ALTER Database...REMOVE FILE を使用して、このコンテナーを削除します。

例

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

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

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B. 2 つのファイルから成るファイルグループをデータベースに追加する

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

USE master
GO
ALTER DATABASE AdventureWorks2022
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),  
(  
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

C. 2 つのログファイルをデータベースに追加する

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

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD LOG FILE
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D. データベースからファイルを削除する

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

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

E. ファイルを変更する

次の例では、例 B で追加したファイルのうち、1 つのサイズを拡張します。MODIFY FILE を指定した ALTER DATABASE コマンドではファイルサイズを大きくすることだけが可能なため、ファイルサイズを小さくする必要がある場合は、DBCC SHRINKFILE を使用する必要があります。

USE master;
GO

ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

この例では、データファイルのサイズを 100 MB に縮小してから、その量のサイズを指定します。

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

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

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

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

Note

この例を実行する前に、ファイルを新しいディレクトリに物理的に移動しておく必要があります。その後、SQL Server のインスタンスをいったん停止してから起動するか、または AdventureWorks2022 データベースをいったん OFFLINE にしてから ONLINE にして、変更を実装します。

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

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

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

tempdb データベースの論理ファイル名と、ディスク上での現在の場所を確認します。
```
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
```

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

SQL Serverのインスタンスをいったん停止してから再起動します。

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

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

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

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

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

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

I. ALTER DATABASE を使用してファイルグループを追加する

次の例では、FILEGROUP 句が含まれる FILESTREAM を、FileStreamPhotoDB データベースに追加します。

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause.
ALTER DATABASE FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM;
GO

--Add a file for storing database photos to FILEGROUP
ALTER DATABASE FileStreamPhotoDB
ADD FILE
(
  NAME= 'PhotoShoot1',
  FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot;
GO

次の例では、FILEGROUP 句が含まれる MEMORY_OPTIMIZED_DATA を、xtp_db データベースに追加します。このファイルグループには、メモリ最適化データを格納します。

--Create and add a FILEGROUP that CONTAINS the MEMORY_OPTIMIZED_DATA clause.
ALTER DATABASE xtp_db
ADD FILEGROUP xtp_fg
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

--Add a file for storing memory optimized data to FILEGROUP
ALTER DATABASE xtp_db
ADD FILE
(
  NAME='xtp_mod',
  FILENAME='d:\data\xtp_mod'
)
TO FILEGROUP xtp_fg;
GO

J. ファイルグループ内のファイルが自動拡張のしきい値を満たす場合にファイルグループ内のすべてのファイルが拡張されるようにファイルグループを変更する

次の例では、必要な ALTER DATABASE ステートメントを生成して、読み取り/書き込みファイルグループを AUTOGROW_ALL_FILES 設定で変更します。

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements
--so that all read-write filegroups grow at the same time.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, isdone BIT);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, fgname sysname, isdone BIT);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid INT, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
  SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

  SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
  INSERT INTO #tmpfgs
  EXEC (@query)

  UPDATE #tmpdbs
  SET isdone = 1
  WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
  WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
  BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

    SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

    PRINT @query

    UPDATE #tmpfgs
    SET isdone = 1
    WHERE [dbid] = @dbid AND fgname = @fgname
  END
END;
GO

参照

SQL Server

* SQL Managed Instance *

Azure SQL Managed Instance

このステートメントは、Azure SQL Managed Instance 内のデータベースで使用します。

Azure SQL Managed Instance の構文

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ]
        [ TO FILEGROUP { filegroup_name } ]
  | REMOVE FILE logical_file_name
  | MODIFY FILE <filespec>
}

<filespec>::=
(
    NAME = logical_file_name
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
)

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option>
        | DEFAULT
        | NAME = new_filegroup_name
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }
        }
}  
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE }
    | { READ_ONLY | READ_WRITE }
}

引数

<add_or_modify_files>::=

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

database_name: 変更するデータベースの名前です。

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

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

<filespec>::=

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

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

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

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

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

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

MAXSIZE { max_size| UNLIMITED }: ファイルのサイズの上限を指定します。

UNLIMITED: ディスクがいっぱいになるまでファイルを拡張するように指定します。 SQL Server では、無制限に拡張するファイル固有のログの最大サイズは 2 TB で、データファイルの最大サイズは 16 TB です。

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

growth_increment: 新しい領域が必要とされるたびにファイルに追加される領域の容量です。

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

FILEGROWTH が指定されていない場合、既定値は次のとおりです。

データ 16 MB
ログファイル 16 MB

<add_or_modify_filegroups>::=

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

ADD FILEGROUP filegroup_name: データベースにファイルグループを追加します。

次の例では、sql_db_mi というデータベースに追加するファイルグループを作成し、そのファイルグループにファイルを追加します。

ALTER DATABASE sql_db_mi ADD FILEGROUP sql_db_mi_fg;
GO
ALTER DATABASE sql_db_mi ADD FILE (NAME='sql_db_mi_mod') TO FILEGROUP sql_db_mi_fg;

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

NAME = new_filegroup_name: ファイルグループ名を new_filegroup_name に変更します。

AUTOGROW_SINGLE_FILE

ファイルグループ内のファイルが自動拡張のしきい値を満たす場合は、そのファイルのみが拡張されます。これは既定値です。

AUTOGROW_ALL_FILES

ファイルグループ内のファイルが自動拡張のしきい値を満たすときに、ファイルグループ内のすべてのファイルを拡張します。

<filegroup_updatability_option>::=

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

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

システム起動時に自動復旧がスキップされます。
データベースの圧縮が不可能になります。
読み取り専用データベースでは、ロックは発生しません。これにより、クエリのパフォーマンスが向上することがあります。

Note

キーワード READONLY は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では READONLY の使用は避け、現在 READONLY を使用しているアプリケーションは修正するようにしてください。代わりに、READ_ONLY を使用してください。

Note

解説

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

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

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

例

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

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

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
  NAME = Test1dat2,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B. 2 つのファイルから成るファイルグループをデータベースに追加する

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

USE master
GO
ALTER DATABASE AdventureWorks2022
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = test1dat3,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

C. データベースからファイルを削除する

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

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

D. ファイルを変更する

USE master;
GO

ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

この例では、データファイルのサイズを 100 MB に縮小してから、その量のサイズを指定します。

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

USE master;
GO

ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

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

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

F. ALTER DATABASE を使用してファイルグループを追加する

次の例では、FILEGROUP を MyDB データベースに追加します。

--Create and add a FILEGROUP
ALTER DATABASE MyDB
ADD FILEGROUP NewFG;
GO

--Add a file to FILEGROUP
ALTER DATABASE MyDB
ADD FILE
(
    NAME= 'MyFile',
)
TO FILEGROUP NewFG;
GO

G. ファイルグループ内のファイルが自動拡張のしきい値を満たす場合にファイルグループ内のすべてのファイルが拡張されるようにファイルグループを変更する

次の例では、必要な ALTER DATABASE ステートメントを生成して、読み取り/書き込みファイルグループを AUTOGROW_ALL_FILES 設定で変更します。

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements
--so that all read-write filegroups grow at the same time.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, isdone BIT);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, fgname sysname, isdone BIT);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid INT, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

    SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
    INSERT INTO #tmpfgs
    EXEC (@query)

    UPDATE #tmpdbs
    SET isdone = 1
    WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
    WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
    BEGIN
        SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

        SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

        PRINT @query

        UPDATE #tmpfgs
        SET isdone = 1
        WHERE [dbid] = @dbid AND fgname = @fgname
    END
END;
GO

ALTER DATABASE (Transact-SQL) の File および Filegroup オプション

製品を選択する

構文

引数

解説

ファイルの移動

ファイルの初期化

FILESTREAM コンテナーの削除

例

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

B. 2 つのファイルから成るファイルグループをデータベースに追加する

C. 2 つのログファイルをデータベースに追加する

D. データベースからファイルを削除する

E. ファイルを変更する

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

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

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

I. ALTER DATABASE を使用してファイルグループを追加する

J. ファイルグループ内のファイルが自動拡張のしきい値を満たす場合にファイルグループ内のすべてのファイルが拡張されるようにファイルグループを変更する

参照

Azure SQL Managed Instance

Azure SQL Managed Instance の構文

引数

解説

例

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

B. 2 つのファイルから成るファイルグループをデータベースに追加する

C. データベースからファイルを削除する

D. ファイルを変更する

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

F. ALTER DATABASE を使用してファイルグループを追加する

G. ファイルグループ内のファイルが自動拡張のしきい値を満たす場合にファイルグループ内のすべてのファイルが拡張されるようにファイルグループを変更する

参照

フィードバック

フィードバック

その他のリソース

ALTER DATABASE (Transact-SQL) の File および Filegroup オプション

製品を選択する

構文

引数

解説

ファイルの移動

ファイルの初期化

FILESTREAM コンテナーの削除

例

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

B. 2 つのファイルから成るファイル グループをデータベースに追加する

C. 2 つのログ ファイルをデータベースに追加する

D. データベースからファイルを削除する

E. ファイルを変更する

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

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

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

I. ALTER DATABASE を使用してファイル グループを追加する

J. ファイル グループ内のファイルが自動拡張のしきい値を満たす場合にファイル グループ内のすべてのファイルが拡張されるようにファイル グループを変更する

参照

Azure SQL Managed Instance

Azure SQL Managed Instance の構文

引数

解説

例

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

B. 2 つのファイルから成るファイル グループをデータベースに追加する

C. データベースからファイルを削除する

D. ファイルを変更する

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

F. ALTER DATABASE を使用してファイル グループを追加する

G. ファイル グループ内のファイルが自動拡張のしきい値を満たす場合にファイル グループ内のすべてのファイルが拡張されるようにファイル グループを変更する

参照

フィードバック

フィードバック

その他のリソース

B. 2 つのファイルから成るファイルグループをデータベースに追加する

C. 2 つのログファイルをデータベースに追加する

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

I. ALTER DATABASE を使用してファイルグループを追加する

J. ファイルグループ内のファイルが自動拡張のしきい値を満たす場合にファイルグループ内のすべてのファイルが拡張されるようにファイルグループを変更する

B. 2 つのファイルから成るファイルグループをデータベースに追加する

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

F. ALTER DATABASE を使用してファイルグループを追加する

G. ファイルグループ内のファイルが自動拡張のしきい値を満たす場合にファイルグループ内のすべてのファイルが拡張されるようにファイルグループを変更する