DBCC SHRINKDATABASE (Transact-SQL)

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

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

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

構文

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 ]

注意

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

引数

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 権限が必要です。

結果セット

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

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

Note

データベースエンジンでは圧縮されないファイルの行は表示されません。

解説

注意

Azure Synapse では、shrink コマンドの実行は、I/O が多数発生する操作であり、専用 SQL プール (旧 SQL DW) がオフラインになる可能性があるため、推奨されません。また、このコマンドを実行すると、データウェアハウススナップショットのコストに影響します。

特定のデータベースに関するすべてのデータとログファイルを圧縮するには、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);

次の方法で共有

DBCC SHRINKDATABASE (Transact-SQL)

構文

引数

database_name | database_id | 0

target_percent

NOTRUNCATE

TRUNCATEONLY

WITH NO_INFOMSGS

縮小操作を使用した WAIT_AT_LOW_PRIORITY

WAIT_AT_LOW_PRIORITY

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

結果セット

解説

DBCC SHRINKDATABASE の動作

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

ベストプラクティス

トラブルシューティング

アクセス許可

例

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

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

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

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

関連項目

次の手順

フィードバック

その他のリソース

次の方法で共有

DBCC SHRINKDATABASE (Transact-SQL)

構文

引数

database_name | database_id | 0

target_percent

NOTRUNCATE

TRUNCATEONLY

WITH NO_INFOMSGS

縮小操作を使用した WAIT_AT_LOW_PRIORITY

WAIT_AT_LOW_PRIORITY

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

結果セット

解説

DBCC SHRINKDATABASE の動作

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

ベスト プラクティス

トラブルシューティング

アクセス許可

例

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

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

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

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

関連項目

次の手順

フィードバック

その他のリソース

ベストプラクティス

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