DBCC SHRINKFILE (Transact-SQL)

[アーティクル]
03/18/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance

現在のデータベースに指定されているデータまたはログファイルのサイズを圧縮します。同じファイルグループ内のあるファイルから他のファイルにデータを移動するために使用できます。この処理で、ファイルを空にしてデータベースを削除することができます。ファイルを作成時のサイズよりも圧縮し、最小ファイルサイズを新しい値にリセットすることができます。

Transact-SQL 構文表記規則

構文

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH 
  {     
      [ WAIT_AT_LOW_PRIORITY 
        [ ( 
            <wait_at_low_priority_option_list>
        )] 
      ] 
      [ , NO_INFOMSGS]
  }
]
       
< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>
 
< wait_at_low_priority_option > ::=
    ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Note

SQL Server 2014 以前の Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

file_name

圧縮するファイルの論理名。

file_id

圧縮するファイルの識別 (ID) 番号。ファイル ID を取得するには、システム関数 FILE_IDEX を使用するか、現在のデータベースで sys.database_files カタログビューに対してクエリを実行します。

target_size

ファイルの新しいサイズ (MB 単位) を表す整数。指定されていないか、0 の場合、DBCC SHRINKFILE はファイル作成サイズに減らされます。

DBCC SHRINKFILE <target_size> を使用して、空のファイルの既定サイズを減らすことができます。たとえば、5 MB のファイルを作成してから、ファイルがまだ空のうちに 3 MB に圧縮した場合、既定のファイルサイズは 3 MB に設定されます。これは、データが含まれたことがない空のファイルにのみ該当します。

このオプションは、FILESTREAM ファイルグループコンテナーではサポートされていません。

指定した場合、DBCC SHRINKFILE では、target_size までファイルの圧縮が試行されます。解放対象のファイルの領域内にある使用ページは、ファイルの保持領域内の空き領域に移動されます。たとえば、10 MB のデータファイルで、8 target_size を指定した DBCC SHRINKFILE 操作を実行すると、ファイルの末尾 2 MB 内にあるすべての使用ページがファイルの先頭 8 MB にある未割り当てページに移動されます。 DBCC SHRINKFILE では、必要な格納データサイズ以下にファイルが圧縮されません。たとえば、10 MB のデータファイルのうち 7 MB が使用されている場合、target_size を 6 にして DBCC SHRINKFILE ステートメントを実行しても、ファイルは 7 MB にまでしか圧縮されず、6 MB にはなりません。

EMPTYFILE

指定したファイルから、同じファイルグループ内の他のファイルにすべてのデータを移動します。つまり、EMPTYFILE では、指定したファイルから、同じファイルグループ内の他のファイルにデータを移動します。 EMPTYFILE を使用すると、このファイルが読み取り専用でなくても、ファイルに新しいデータが追加されません。ファイルを削除するには ALTER DATABASE ステートメントを使用できます。 ALTER DATABASE ステートメントを使用してファイルサイズを変更すると、読み取り専用フラグがリセットされ、データを追加することができます。

FILESTREAM ファイルグループコンテナーでは、FILESTREAM ガベージコレクターが実行され、EMPTYFILE によって他のコンテナーにコピーされた不要なすべてのファイルグループコンテナーファイルが削除された後でなければ、ALTER DATABASE を使用してファイルを削除できません。詳細については、「sp_filestream_force_garbage_collection」を参照してください。 FILESTREAM コンテナーの削除については、「ALTER DATABASE の File および Filegroup オプション (Transact-SQL)」の対応するセクションを参照してください。

NOTRUNCATE

データファイル末尾の割り当て済みページをファイル先頭の未割り当てページに移動します。必要に応じて target_percent を指定することもできます。ファイル末尾の空き領域はオペレーティングシステムに返されず、ファイルの物理サイズは変わりません。したがって、NOTRUNCATE を指定した場合は、ファイルが圧縮されていないように見えます。

NOTRUNCATE はデータファイルにのみ適用されます。ログファイルは影響を受けません。

このオプションは、FILESTREAM ファイルグループコンテナーではサポートされていません。

TRUNCATEONLY

ファイル末尾のすべての空き領域がオペレーティングシステムに解放されますが、ファイル内でのページの移動は行われません。データファイルは、最後に割り当てられたエクステントを限度として圧縮されます。

target_size は、TRUNCATEONLY と共に指定した場合、無視されます。

TRUNCATEONLY オプションでは、ログ内で情報を移動しませんが、非アクティブな VLF をログファイルの末尾から削除します。このオプションは、FILESTREAM ファイルグループコンテナーではサポートされていません。

WITH NO_INFOMSGS

すべての情報メッセージを表示しないようにします。

縮小操作を使用した WAIT_AT_LOW_PRIORITY

適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL Database、Azure SQL Managed Instance

低優先度での待機機能により、ロックの競合が軽減されます。詳細については、「DBCC SHRINKDATABASE に関するコンカレンシーの問題を理解する」を参照してください。

この機能は、オンラインインデックス操作の WAIT_AT_LOW_PRIORITY に似ていますが、いくつかの違いがあります。

ABORT_AFTER_WAIT オプションを NONE に指定することはできません。

WAIT_AT_LOW_PRIORITY

適用対象: SQL Server (SQL Server 2022 (16.x) 以降) と Azure SQL Database。

縮小コマンドを WAIT_AT_LOW_PRIORITY モードで実行する場合、スキーマの安定性 (Sch-S) ロックを必要とする新しいクエリは、縮小操作が待機を停止して実行を開始するまで、待機中の圧縮操作によってブロックされません。縮小操作は、スキーマ変更 (Sch-M) ロックを取得できる場合に実行されます。 WAIT_AT_LOW_PRIORITY モードの新しい縮小操作が実行時間の長いクエリのためにロックを取得できない場合、縮小操作は最終的に既定で 1 分後にタイムアウトし、通知せずに終了します。

WAIT_AT_LOW_PRIORITY モードの新しい縮小操作が実行時間の長いクエリのためにロックを取得できない場合、縮小操作は最終的に既定で 1 分後にタイムアウトし、通知せずに終了します。これは、Sch-S ロックを保持している同時実行クエリまたはクエリが原因で、縮小操作が Sch-M ロックを取得できない場合に発生します。タイムアウトが発生すると、エラー 49516 メッセージが SQL Server エラーログに送信されます (例: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5)。この時点で、アプリケーションに影響がないことを確認して、WAIT_AT_LOW_PRIORITY モードで縮小操作をもう一度試すことができます。

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

適用対象: SQL Server (SQL Server 2022 (16.x) 以降) と Azure SQL Database。

SELF

いずれのアクションも行わずに、現在実行中のファイルの圧縮操作を終了します。
BLOCKERS

ファイルの圧縮操作をブロックしているすべてのユーザートランザクションを強制終了して、操作を続行できるようにします。 BLOCKERS オプションを使用するには、ログインに ALTER ANY CONNECTION 権限が必要です。

結果セット

次の表は結果セットの列を示しています。

列名	説明
DbId	データベースエンジンで圧縮が試行されたファイルのデータベース識別番号。
FileId	データベースエンジンで圧縮が試行されたファイルのファイル識別番号。
CurrentSize	ファイルが現在占有する 8 KB ページの数。
MinimumSize	ファイルが占有できる 8 KB ページの最小数。この数値は、ファイルの最小サイズまたは最初に作成されたサイズと一致します。
UsedPages	ファイルが現在使用している 8 KB ページの数。
EstimatedPages	データベースエンジンで推定されるファイル圧縮後の 8 KB ページの数。

解説

DBCC SHRINKFILE は現在のデータベース内のファイルに適用されます。現在のデータベースを変更する方法の詳細については、「USE (Transact-SQL)」を参照してください。

DBCC SHRINKFILE 操作は、どの時点でも停止でき、完了していた作業は保持されます。 EMPTYFILE パラメーターを使用し、操作をキャンセルした場合、さらにデータが追加されないように、ファイルがマークされることはありません。

DBCC SHRINKFILE 操作が失敗すると、エラーが発生します。

ファイルの圧縮中に他のユーザーはデータベースで作業できます。データベースをシングルユーザーモードにする必要はありません。システムデータベースを圧縮するために、SQL Server インスタンスをシングルユーザーモードで実行する必要はありません。

WAIT_AT_LOW_PRIORITY と共に指定する場合、圧縮操作の Sch-M ロック要求は、1 分間コマンドを実行するときに低優先度で待機します。操作がその間ブロックされた場合は、指定されている ABORT_AFTER_WAIT アクションが実行されます。

DBCC SHRINKFILE に関するコンカレンシーの問題を理解する

データベースの縮小コマンドと縮小ファイルコマンドは、特にインデックスの再構築などのアクティブなメンテナンスや、ビジー状態の OLTP 環境でのコンカレンシーの問題につながる場合があります。アプリケーションがデータベーステーブルに対してクエリを実行すると、クエリが操作を完了するまで、これらのクエリはスキーマ安定性ロック (Sch-S) を取得して維持します。通常の使用中に領域を再利用しようとすると、データベースの縮小、およびファイルの縮小操作では、現在、Index Allocation Map (IAM) ページを移動または削除するときにスキーマ変更ロック (Sch-M) が必要になり、ユーザークエリで必要な Sch-S ロックがブロックされます。その結果、実行時間の長いクエリでは、クエリが完了するまで縮小操作がブロックされます。これは、Sch-S ロックを必要とする新しいクエリも、待機中の縮小操作の背後でキューに登録され、ブロックもされることを意味し、このコンカレンシーの問題がさらに悪化します。これは、アプリケーションクエリのパフォーマンスに大きな影響を与えるおそれがあり、データベースファイルを縮小するために必要なメンテナンスを完了するのにも問題が発生します。 SQL Server 2022 (16.x) で導入された、低優先度での縮小機能では、WAIT_AT_LOW_PRIORITY モードでスキーマ変更ロックを取得することで、この問題に対処します。詳細については、「縮小操作を使用した WAIT_AT_LOW_PRIORITY」を参照してください。

Sch-S および Sch-M ロックの詳細については、「トランザクションのロックおよび行のバージョン管理ガイド」を参照してください。

ログファイルを圧縮する

ログファイルの場合、データベースエンジンは target_size を使用してログ全体の目標サイズを計算します。そのため、target_size は圧縮操作後のログの空き領域です。ログ全体の目標サイズは、各ログファイルの目標サイズに変換されます。 DBCC SHRINKFILE では、各物理ログファイルの目標サイズへの圧縮がすぐに試行されます。ただし、論理ログの一部が、目標サイズを超える仮想ログ内に存在する場合は、データベースエンジンにより、できるだけ多くの領域が解放され、情報メッセージが発行されます。このメッセージには、ファイルの末尾で仮想ログから論理ログを移動するために行う必要のある操作が説明されています。この操作を実行した後、DBCC SHRINKFILE を使って、残りの領域を解放できます。

ログファイルは仮想ログファイルの境界を越えて圧縮できないため、ログファイルを仮想ログファイルのサイズより小さく圧縮することはできません。これはログファイルが使用されていない場合でも同じです。データベースエンジンでは、ログファイルの作成時または拡張時に、仮想ファイルのログサイズを動的に選択します。

ベストプラクティス

ファイルを圧縮する場合は次のことを考慮してください。

圧縮操作は、テーブルの切り捨てやテーブルの削除操作など、大きな未使用領域を生成する操作の後に実行すると最も効果的です。
ほとんどのデータベースでは、毎日の定期的操作で使用するための空き領域が必要です。データベースファイルを繰り返し縮小しても、データベースのサイズが再び大きくなっていることがある場合は、通常の操作に空き領域が必要であることを示しています。このような場合、データベースを繰り返し縮小することは無意味な操作です。データベースファイルを拡張するために必要な autogrow イベントは、パフォーマンスの妨げになります。
圧縮操作では、データベース内のインデックスの断片化状態は保持されず、一般に、断片化の程度が大きくなります。この断片化の点からも、データベースを繰り返し圧縮することはお勧めできません。
複数のファイルを同じデータベース内に、同時ではなく、順番に圧縮します。システムテーブルでの競合が原因で、ブロックが発生し、遅延につながる可能性があります。

トラブルシューティング

このセクションでは、DBCC SHRINKFILE コマンドを実行するときに発生する可能性のある問題を診断して修正する方法について説明します。

ファイルが圧縮されない

エラーなしで圧縮操作が完了してもファイルサイズが変わらない場合は、以下の操作でファイルに十分な空き領域があることを確認してください。

次のクエリを実行します。

SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;

DBCC SQLPERF コマンドを実行し、トランザクションログで使用されている領域を返します。

使用できる空き領域が不十分な場合、圧縮操作ではこれ以上ファイルサイズを減らすことはできません。

通常、圧縮されていないように見えるのはログファイルです。この圧縮されない状況は、多くの場合、ログファイルが切り捨てられなかった結果として起こります。ログを切り捨てるために、データベース復旧モデルを SIMPLE に設定するか、ログをバックアップしてからもう一度 DBCC SHRINKFILE 操作を実行できます。

圧縮操作がブロックされる

行のバージョン管理に基づく分離レベルで実行されているトランザクションによって、圧縮操作がブロックされることがあります。たとえば、DBCC SHRINKDATABASE 操作を実行するときに、行のバージョン管理に基づく分離レベルでの大規模な削除操作が進行中の場合、圧縮操作は削除が完了してから続行されます。このブロックが発生すると、DBCC SHRINKFILE および DBCC SHRINKDATABASE 操作によって、情報メッセージ (SHRINKDATABASE は 5202、SHRINKFILE は 5203) が SQL Server エラーログに出力されます。このメッセージは、最初の 1 時間は 5 分ごと、それ以降は 1 時間ごとにログに記録されます。たとえば、エラーログに次のエラーメッセージが含まれている場合は、次のエラーが発生します。

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

このメッセージは、109 (圧縮操作が完了した最後のトランザクション) よりもタイムスタンプが古いスナップショットトランザクションによって圧縮操作がブロックされていることを意味します。また、sys.dm_tran_active_snapshot_database_transactions 動的管理ビューの transaction_sequence_num 列または first_snapshot_sequence_num 列に、値 15 が含まれることも示しています。 transaction_sequence_num または first_snapshot_sequence_num のいずれかのビュー列に、圧縮操作の最後に完了したトランザクション (109) より低い番号が含まれている場合、それらのトランザクションが完了するまで圧縮操作は待機状態になります。

この問題を解決するには、次のいずれかの作業を実行します。

圧縮操作をブロックしているトランザクションを終了します。
圧縮操作を終了します。圧縮操作が終了した場合、完了済みの作業は保持されます。
何もせず、ブロックしているトランザクションが完了するまで圧縮操作を待機状態にしておきます。

アクセス許可

sysadmin 固定サーバーロールまたは db_owner 固定データベースロールのメンバーシップが必要です。

例

A. 指定した目標サイズにデータファイルを縮小する

次の例では、UserDB ユーザーデータベース内の DataFile1 というデータファイルのサイズを 7 MB に圧縮します。

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. 指定した目標サイズにログファイルを縮小する

次の例では、AdventureWorks2022 データベース内のログファイルを 1 MB に圧縮します。 DBCC SHRINKFILE コマンドを使用してファイルを圧縮できるようにするには、まずデータベース復旧モデルを SIMPLE に設定してファイルを切り捨てます。

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. データファイルを切り捨てる

次の例では、AdventureWorks2022 データベース内のプライマリデータファイルを切り捨てます。 sys.database_files カタログビューに対してクエリを実行し、データファイルの file_id を取得します。

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. ファイルを空にする

次の例では、データベースから削除できるようファイルを空にする方法を示します。この例の目的として、データファイルは最初に作成され、データが含まれています。

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

E. WAIT_AT_LOW_PRIORITY を使用してデータベースファイルを縮小する

次の例では、現在のユーザーデータベースのデータファイルのサイズを 1 MB まで縮小することを試みます。 sys.database_files カタログビューに対してクエリを実行し、データファイルの file_id (この例では file_id 5.) を取得します。 1 分以内にロックを取得できない場合、縮小操作は中止されます。

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

次の方法で共有

DBCC SHRINKFILE (Transact-SQL)

構文

引数

file_name

file_id

target_size

EMPTYFILE

NOTRUNCATE

TRUNCATEONLY

WITH NO_INFOMSGS

縮小操作を使用した WAIT_AT_LOW_PRIORITY

WAIT_AT_LOW_PRIORITY

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

結果セット

解説

DBCC SHRINKFILE に関するコンカレンシーの問題を理解する

ログファイルを圧縮する

ベストプラクティス

トラブルシューティング

ファイルが圧縮されない

圧縮操作がブロックされる

アクセス許可

例

A. 指定した目標サイズにデータファイルを縮小する

B. 指定した目標サイズにログファイルを縮小する

C. データファイルを切り捨てる

D. ファイルを空にする

E. WAIT_AT_LOW_PRIORITY を使用してデータベースファイルを縮小する

関連項目

次の手順

フィードバック

その他のリソース

次の方法で共有

DBCC SHRINKFILE (Transact-SQL)

構文

引数

file_name

file_id

target_size

EMPTYFILE

NOTRUNCATE

TRUNCATEONLY

WITH NO_INFOMSGS

縮小操作を使用した WAIT_AT_LOW_PRIORITY

WAIT_AT_LOW_PRIORITY

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

結果セット

解説

DBCC SHRINKFILE に関するコンカレンシーの問題を理解する

ログ ファイルを圧縮する

ベスト プラクティス

トラブルシューティング

ファイルが圧縮されない

圧縮操作がブロックされる

アクセス許可

例

A. 指定した目標サイズにデータ ファイルを縮小する

B. 指定した目標サイズにログ ファイルを縮小する

C. データ ファイルを切り捨てる

D. ファイルを空にする

E. WAIT_AT_LOW_PRIORITY を使用してデータベース ファイルを縮小する

関連項目

次の手順

フィードバック

その他のリソース

ログファイルを圧縮する

ベストプラクティス

A. 指定した目標サイズにデータファイルを縮小する

B. 指定した目標サイズにログファイルを縮小する

C. データファイルを切り捨てる

E. WAIT_AT_LOW_PRIORITY を使用してデータベースファイルを縮小する