DBCC SHRINKDATABASE (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

指定したデータベース内のデータ ファイルとログ ファイルのサイズを圧縮します。

Transact-SQL 構文表記規則

構文

SQL Server の構文:

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { 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 }

Azure Synapse Analytics の構文:

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

引数

database_name | database_id | 0

圧縮するデータベースの名前または ID。 0 は、現在のデータベースを使用するように指定します。

target_percent

データベースを圧縮した後、データベース ファイル内に残す空き領域のパーセンテージ。

NOTRUNCATE

ファイル末尾の割り当て済みページをファイル先頭の未割り当てページに移動します。 この操作により、ファイル内のデータが圧縮されます。 target_percent は省略可能です。 Azure Synapse Analytics では、このオプションはサポートされません。

ファイル末尾の空き領域はオペレーティング システムに返されず、ファイルの物理サイズは変わりません。 そのため、NOTRUNCATE を指定した場合、データベースが圧縮されていないように見えます。

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

TRUNCATEONLY

ファイル末尾のすべての空き領域をオペレーティング システムに解放します。 ファイル内でのページの移動は行いません。 データ ファイルは、最後に割り当てられたエクステントを限度として圧縮されます。 target_percent が TRUNCATEONLY と共に指定された場合は、無視されます。 Azure Synapse Analytics では、このオプションはサポートされません。

TRUNCATEONLY オプションを指定して DBCC SHRINKDATABASE を使うと、データベースのトランザクション ログ ファイルにのみ影響します。 データ ファイルを切り捨てるには、代わりに DBCC SHRINKFILE を使ってください。 詳細については、DBCC SHRINKFILE に関するページを参照してください。

WITH NO_INFOMSGS

重大度レベル 0 から 10 のすべての情報メッセージを表示しないようにします。

縮小操作を使用した 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

縮小コマンドを 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 ]

• SELF

  SELF は既定のオプションです。 いずれのアクションも行わずに、現在実行中のデータベースの圧縮操作を終了します。

• BLOCKERS

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

結果セット

次の表では、結果セットの列について説明します。

解説

特定のデータベースに関するすべてのデータとログ ファイルを圧縮するには、DBCC SHRINKDATABASE コマンドを実行します。 特定のデータベースで一度に 1 つのデータまたはログ ファイルを圧縮するには、DBCC SHRINKFILE コマンドを実行します。

データベースの空き (未割り当て) 領域の現在の量を表示するには、sp_spaceused を実行します。

DBCC SHRINKDATABASE 操作は、プロセスのどの時点でも停止でき、完了していた作業は保持されます。

データベースは、そのデータベースの構成された最小サイズより小さくすることはできません。 データベースの最初の作成時に、最小サイズを指定します。 または、ファイル サイズ変更操作を使用して最後に明示的に設定したサイズを最小サイズにすることができます。 ファイル サイズ変更操作の例として、DBCC SHRINKFILE や ALTER DATABASE のような操作があります。

たとえば、データベースの最初の作成時にサイズを 10 MB に指定したとします。 その後、100 MB まで拡張したとします。 データベース内のすべてのデータを削除したとしても、データベースを縮小できる限界は 10 MB です。

DBCC SHRINKDATABASE を実行するときは、NOTRUNCATE オプションまたは TRUNCATEONLY オプションのいずれかを指定します。 そうしない場合、NOTRUNCATE を指定して DBCC SHRINKDATABASE 操作を実行した後に、TRUNCATEONLY を指定して DBCC SHRINKDATABASE 操作を実行した場合と同じ結果になります。

圧縮されるデータベースは、シングル ユーザー モードになっていなくてもかいません。 データベースを圧縮している最中でも、他のユーザーがそのデータベースで作業することができます。システム データベースの場合も同様です。

データベースのバックアップ中、データベースを圧縮することはできません。 逆に、データベースの圧縮操作の進行中、データベースをバックアップすることはできません。

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

DBCC SHRINKDATABASE の動作

DBCC SHRINKDATABASE では、データ ファイルはファイルごとに圧縮されますが、ログ ファイルはすべてが 1 つの連続的なログ プールに存在するものとして圧縮されます。 ファイルは常に末尾から圧縮されます。

いくつかのログ ファイルと 1 つのデータ ファイルがあり、mydb というデータベースがあるとします。 各データ ファイルとログ ファイルのサイズは 10 MB で、データ ファイルに 6 MB のデータが含まれているとします。 それぞれのファイルについて、データベース エンジンによって目標サイズが計算されます。 この値はファイルの圧縮後のサイズです。 DBCC SHRINKDATABASE を target_percent と共に指定すると、データベース エンジンでは、圧縮後にファイル内の空き領域が target_percent の量になるように目標サイズが計算されます。

たとえば、mydb を圧縮する場合に target_percent を 25 に指定すると、データベース エンジンではデータ ファイルの目標サイズが 8 MB (6 MB のデータに 2 MB の空き領域を加えたもの) と計算されます。 したがって、データベース エンジンでは、データ ファイルの末尾 2 MB にあるすべてのデータがデータ ファイルの先頭 8 MB にある空き領域に移動されてから、ファイルが圧縮されます。

次に、mydb のデータ ファイルに 7 MB のデータが含まれているとします。 target_percent を 30 に指定した場合、このデータ ファイルは空き領域のパーセンテージが 30 になるように圧縮されます。 ただし、target_percent を 40 に指定しても、データ ファイルの現在の合計サイズに十分な空き領域を作成できないため、データ ファイルは圧縮されません。

別の考え方をすれば、目的の空き領域 40% にデータ ファイル最大容量の 70% (10 MB 中の 7 MB) を加算すると、100% を超えます。 30 より大きい値を target_percent に指定すると、データ ファイルは圧縮されません。 圧縮されない理由は、目的の空き領域のパーセンテージと、データ ファイルの現在の占有パーセンテージを加算した値が 100% を超えることです。

ログ ファイルの場合、データベース エンジンでは target_percent を使用してログ全体の目標サイズを計算します。 target_percent は圧縮操作後のログ内の空き領域の量になります。 その後、ログ全体の目標サイズは各ログ ファイルの目標サイズに変換されます。

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

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

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

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

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

ベスト プラクティス

データベースを圧縮する場合は次のことを考慮してください。

• 圧縮操作は、テーブルの切り捨てやテーブルの削除の操作など、未使用領域を作成する操作の後が最も効果的です。
• ほとんどのデータベースでは、毎日の定期的操作で使用するための空き領域が必要です。 データベース ファイルを繰り返し縮小しても、データベースのサイズが再び大きくなっていることがある場合は、通常の操作に空き領域が必要であることを示しています。 このような場合、データベースを繰り返し縮小することは無意味な操作です。 データベース ファイルを拡張するために必要な autogrow イベントは、パフォーマンスの妨げになります。
• 圧縮操作では、データベース内のインデックスの断片化状態は保持されず、一般に、断片化の程度が大きくなります。 この結果からも、データベースを繰り返し圧縮することはお勧めできません。
• 特別な要件がない限り、AUTO_SHRINK データベース オプションを ON に設定しないでください。

トラブルシューティング

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

DBCC SHRINKDATABASE for database ID 9 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 (Transact-SQL) 動的管理ビューの transaction_sequence_num 列または first_snapshot_sequence_num 列に、値 15 が含まれることも示しています。 そのビューの transaction_sequence_num 列または first_snapshot_sequence_num 列に、圧縮操作により完了した最後のトランザクション (109) より低い番号が含まれている場合があります。 その場合は、それらのトランザクションが終了するまで圧縮操作は待機状態となります。

この問題を解決するために、次のいずれかの作業を実行できます。

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

アクセス許可

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

例

A. データベースを圧縮し、空き領域のパーセンテージを指定する

次の例では、UserDB ユーザー データベース内のデータ ファイルとログ ファイルのサイズを圧縮して、データベースの空き領域が 10% になるようにします。

DBCC SHRINKDATABASE (UserDB, 10);
GO

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

次の例では、AdventureWorks2022 サンプル データベース内のデータ ファイルとログ ファイルを、最後に割り当てられたエクステントまで圧縮します。

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Azure Synapse Analytics データベースを縮小する

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

D. WAIT_AT_LOW_PRIORITY を使用してデータベースを縮小する

次の例では、AdventureWorks2022 データベース内のデータ ファイルとログ ファイルのサイズを圧縮して、データベースの空き領域が 20% になるようにします。 1 分以内にロックを取得できない場合、縮小操作は中止されます。

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

関連項目

• SQL Server での autogrow と autoshrink の設定に関する考慮事項
• データベース ファイルとファイル グループ
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)

次の手順

• ALTER DATABASE (Transact-SQL)
• DBCC SHRINKFILE (Transact-SQL)
• データベースの圧縮
• ファイルの縮小