SQL Server 2005 分區表自動化清理方案：交換分區釋放空間

面對 800GB 資料庫、每日新增 120 萬列 (約 7GB) 的高增長場景，磁碟空間即將耗盡。利用分區表 SWITCH PARTITION 特性，將最舊分區與空表交換並刪除，瞬間釋放空間並回收分區供新資料使用。本文提供完整自動化設計：管理表、預存程序、Agent 作業排程。

環境與前提

• 分區欄位：自增 ID (INT)
• 分區索引與資料儲存對齊 (Aligned)
• 分區函數：Fun_Archive_Id、分區方案：Sch_Archive_Id
• 檔案群組命名：FG_Archive_Id_01, FG_Archive_Id_02...

設計架構

1. 分區管理表：PartitionManage

CREATE TABLE [dbo].[PartitionManage] (
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Change_Value] INT NULL,      -- 觸發清理的 ID 閾值
    [Part_Value] INT NULL,        -- 對應分區邊界值
    [IsDone] BIT NULL             -- 是否已處理
) ON [PRIMARY];

2. 初始化分區閾值資料

DECLARE @i INT = 10, @maxValue INT = 100;
WHILE @i <= @maxValue
BEGIN
    INSERT INTO dbo.PartitionManage VALUES (@i - 4, @i, 0); -- Change_Value = Part_Value - 4
    SET @i = @i + 10;
END

意義：當資料表最大 ID 超過 Change_Value，即處理對應 Part_Value 分區。

3. 自動化清理預存程序：sp_PartitionManage

CREATE PROCEDURE [dbo].[sp_PartitionManage]
AS
BEGIN
    DECLARE @Max_value INT, @Change_value INT, @PARTITION_value INT;
    SELECT @Max_value = MAX(Id) FROM [dbo].[Archive];
    SELECT TOP 1 @Change_value = Change_Value, @PARTITION_value = Part_Value
    FROM [dbo].[PartitionManage] WHERE IsDone = 0;

    IF (@Change_value <= @Max_value)  -- 需清理
    BEGIN
        DECLARE @sql VARCHAR(MAX), @Min_Id INT, @PARTITION_num INT, @PARTITION_string VARCHAR(50);

        -- 1. 建立臨時表 (結構與 Archive 相同，含分區對齊)
        SET @sql = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Temp_Archive]'') AND type = ''U'')
            DROP TABLE [dbo].[Temp_Archive]';
        EXEC (@sql);

        SET @sql = 'CREATE TABLE [dbo].[Temp_Archive](
            [Id] INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
            [SiteId] INT NULL, [Title] NVARCHAR(4000) NULL,
            [Author] NVARCHAR(4000) NULL, [Content] NVARCHAR(MAX) NULL,
            CONSTRAINT [PK_Temp_Archive] PRIMARY KEY CLUSTERED ([Id] ASC)
            WITH (PAD_INDEX = ON, FILLFACTOR = 100) ON [Sch_Archive_Id](Id)
        ) ON [Sch_Archive_Id]([Id])';
        EXEC (@sql);

        -- 2. 交換分區資料 (瞬間完成，無資料移動)
        SELECT @Min_Id = MIN(Id) FROM [dbo].[Archive];
        SELECT @PARTITION_num = $PARTITION.Fun_Archive_Id(@Min_Id);
        ALTER TABLE [dbo].[Archive] SWITCH PARTITION @PARTITION_num TO [dbo].[Temp_Archive] PARTITION @PARTITION_num;

        -- 3. 刪除臨時表 (釋放空間)
        DROP TABLE [dbo].[Temp_Archive];

        -- 4. 修改分區方案：指定下一個檔案群組
        SET @PARTITION_string = 'FG_Archive_Id_' + RIGHT('0' + CONVERT(NVARCHAR, @PARTITION_num), 2);
        SET @sql = 'ALTER PARTITION SCHEME [Sch_Archive_Id] NEXT USED [' + @PARTITION_string + ']';
        EXEC (@sql);

        -- 5. 修改分區函數：分割新邊界值 (循環利用分區)
        SET @sql = 'ALTER PARTITION FUNCTION Fun_Archive_Id() SPLIT RANGE (' + CONVERT(VARCHAR(50), @PARTITION_value) + ')';
        EXEC (@sql);

        -- 6. 更新管理表標記完成
        UPDATE [dbo].[PartitionManage] SET IsDone = 1 WHERE Change_Value = @Change_value;
    END
END

4. Agent 作業排程

• 建立作業，步驟執行 EXEC sp_PartitionManage
• 排程：每日低峰期執行 (如 02:00)
• 啟用作業記錄與失敗通知

關鍵技術點解析

監控與驗證

-- 檢視各分區資料量
SELECT $PARTITION.Fun_Archive_Id(Id) AS Partition_num,
       MIN(Id) AS Min_Id, MAX(Id) AS Max_Id, COUNT(*) AS Row_Count
FROM dbo.Archive
GROUP BY $PARTITION.Fun_Archive_Id(Id)
ORDER BY Partition_num;

-- 檢視管理表狀態
SELECT * FROM dbo.PartitionManage ORDER BY Id;

結語

此方案將「人工監控 → 手動執行」轉為「自動偵測 → 自動清理 → 自動循環」，零停機、零資料搬移、空間持續可用。核心在於 SWITCH PARTITION + 分區循環 設計，適用於任何高增長、固定保留期限的時間序列/流水號資料場景。