DBCC SHRINKDATABASEとDBCC SHRINKFILEの違いと実行前に確認すること

-- 対象: 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;