The Difference Between DBCC SHRINKDATABASE and DBCC SHRINKFILE, and What to Check Before Running Them

この文書の適用条件

そのまま実行できるコマンド

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象DBへの接続権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [SampleDB];
GO
SELECT
    f.file_id,
    f.name                                              AS logical_name,
    f.type_desc,                                        -- ROWS（データ）/ LOG（ログ）
    f.physical_name,
    f.size * 8 / 1024                                   AS size_mb,
    CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS bigint) * 8 / 1024 AS used_mb,
    (f.size - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS bigint)) * 8 / 1024 AS free_mb,
    CASE
        WHEN f.is_percent_growth = 1 THEN CAST(f.growth AS varchar(10)) + ' %'
        ELSE CAST(f.growth * 8 / 1024 AS varchar(10)) + ' MB'
    END                                                 AS autogrowth,
    CASE
        WHEN f.max_size IN (-1, 268435456) THEN NULL
        ELSE f.max_size * 8 / 1024
    END                                                 AS max_size_mb
FROM sys.database_files AS f
ORDER BY f.type_desc, f.file_id;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: sys.databases のメタデータ可視性
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    name                  AS database_name,
    recovery_model_desc,
    log_reuse_wait_desc,
    is_auto_shrink_on
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: sysadmin または db_owner
-- 変更作業: あり（ファイル末尾の未使用領域を OS に返却）
-- Production 実行: 可能。ただし log_reuse_wait_desc が NOTHING であることが前提
USE [SampleDB];
GO
-- 末尾の未使用領域だけを解放する（ページ移動を伴わない）
DBCC SHRINKFILE (N'SampleDB_log', TRUNCATEONLY);

-- 目標サイズ（MB）を指定して縮小する
-- DBCC SHRINKFILE (N'SampleDB_log', 1024);

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: sysadmin または db_owner
-- 変更作業: あり（ページ移動によりインデックス断片化が大きく進む）
-- Production 実行: 原則不可。メンテナンス時間帯＋事後のインデックス再構築が前提
USE [SampleDB];
GO
-- 目標サイズ（MB）を指定してデータファイルを縮小する
DBCC SHRINKFILE (N'SampleDB', 8192);

-- 末尾の未使用領域だけを返す（ページ移動なし・断片化の影響が小さい）
-- DBCC SHRINKFILE (N'SampleDB', TRUNCATEONLY);

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: sysadmin または db_owner
-- 変更作業: あり（全ファイル対象のページ移動と縮小）
-- Production 実行: 原則不可。業務停止相当の影響を見込み、事後の再構築計画を用意すること
USE [SampleDB];
GO
-- 第2引数は「縮小後にファイルに残す空き領域の割合(%)」
DBCC SHRINKDATABASE (N'SampleDB', 10);

-- 末尾の未使用領域だけを返す
-- DBCC SHRINKDATABASE (N'SampleDB', TRUNCATEONLY);

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象DBへの接続権限と VIEW DATABASE STATE
-- 変更作業: なし（参照のみ。ただしページを読むため I/O が発生する）
-- Production 実行: 可能。DETAILED は負荷が高いため LIMITED を使うこと
USE [SampleDB];
GO
SELECT
    OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
    OBJECT_NAME(ips.object_id)        AS table_name,
    i.name                            AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
INNER JOIN sys.indexes AS i
        ON ips.object_id = i.object_id
       AND ips.index_id  = i.index_id
WHERE ips.page_count > 1000        -- 小さいインデックスは断片化の影響が小さい
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: sysadmin または db_owner（ALTER 権限）
-- 変更作業: あり（データベースオプションの変更）
-- Production 実行: 可能。変更管理の手順に従うこと
ALTER DATABASE [SampleDB] SET AUTO_SHRINK OFF;

-- 現在の設定を確認する（参照のみ）
SELECT name, is_auto_shrink_on
FROM sys.databases
WHERE database_id > 4;

結果の読み方

こういう状況で使います

• Disk free space is shrinking, and you want to make the database files smaller
• A data file's size did not change even after a large delete
• A temporary process bloated a log file, and you want to shrink it back
• A scheduled shrink job is in place for routine maintenance, but performance keeps gradually degrading

考えられる原因（可能性の高い順）

1. 01

   Unused space left after a large delete or archive

   Deleting data does not automatically shrink the file. The space remains as unused and gets reused by subsequent writes. This state is usually not a problem, and shrinking is not always necessary.

2. 02

   Log bloat from a temporary process

   A large data migration or bulk update can temporarily grow the log. If the process has finished and the size is permanently unneeded, shrinking the log is a reasonable action.

3. 03

   A factor preventing the log from being released still remains

   This includes an open transaction, stopped CDC or replication, or missing log backups. In this state, the log will grow back even after shrinking.

4. 04

   AUTO_SHRINK is enabled

   With auto-shrink enabled, shrinking and autogrowth repeat in a cycle, each time causing fragmentation from page movement and write delays while growth completes. It is disabled by default, and there is rarely a good reason to turn it on.

5. 05

   Shrinking has been built into routine maintenance

   This is a loop of shrink → fragment → rebuild → file growth → shrink. It only consumes I/O and never leads to a lasting improvement.

確認手順

1. 1

   Check free space per file

   参照のみ

   Check `free_mb` via `sys.database_files` and `FILEPROPERTY`. Shrinking accomplishes nothing if free space is small.

2. 2

   Distinguish data files from log files

   参照のみ

   Use `type_desc` to be clear about the target — the two have different criteria for judgment.

3. 3

   For a log, check `log_reuse_wait_desc`

   参照のみ

   If it is not NOTHING, resolve that cause first. Shrinking comes after.

4. 4

   Record the fragmentation percentage before shrinking

   低

   Run `sys.dm_db_index_physical_stats` in `LIMITED` mode to get a baseline for comparison.

5. 5

   Check the autogrowth setting

   参照のみ

   Estimate the cost of regrowth after shrinking. A "%" setting grows by a larger amount each time as the file gets bigger.

6. 6

   Check the AUTO_SHRINK setting

   参照のみ

   If `is_auto_shrink_on` is 1, first consider whether this setting is appropriate.

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• Index fragmentation after shrinking

  Compare before and after in `LIMITED` mode to identify the range needing a rebuild.

• Actual free space on the storage side

  With cloud block storage, shrinking a database file does not necessarily reduce the allocated storage capacity automatically.

• History of autogrowth events

  Check the default trace or the logs for repeated growth after a shrink.

• Filegroups and data placement

  If there are multiple filegroups, check which file has a disproportionate amount of free space before deciding on a target.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• I want to make a data file smaller in SQL Server
• I want to know why shrinking degrades performance
• I want to know how to shrink only the log file

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

Shrinking a file looks like a one-time action, but it actually comes with downstream effects — fragmentation, regrowth, and increased I/O. At GIIP, we keep file size and usage, index fragmentation, and autogrowth events on the same timeline, so we can trace exactly what changed before and after a shrink. Because shrinking is high-impact and irreversible, it is excluded from automatic execution by an AI agent — the decision to act and the timing are left to a human.

関連サービス

• データベース性能チューニング
• 障害対応・火消し支援