DELETE 產生大量交易日誌、鎖升級、執行緒阻塞。WHERE Time < '2006-03-10') 無法直接用 TRUNCATE。TRUNCATE TABLE BigTable;
ALTER TABLE 權限。-- 1. 選出需保留的資料到新表 (最小日誌需 SIMPLE/BULK_LOGGED 模式)
SELECT * INTO TableName_New FROM TableName WHERE Time >= '2006-03-10';
-- 2. 截斷原表 (瞬間完成)
TRUNCATE TABLE TableName;
-- 3. 重命名新表為原表名
EXEC sp_rename 'TableName_New', 'TableName';
優勢: 避開大量 DELETE 日誌爆炸,總耗時 ≈ 複製保留資料時間 + 毫秒級截斷。
注意: SELECT INTO 在 FULL 復原模式下仍會完整記錄日誌,建議暫時切換至 BULK_LOGGED 模式執行,完成後切回 FULL 並立即備份日誌。
-- 前提:表已建立分區函數/方案,按時間欄位分區
-- 1. 建立空的暫存表,結構、索引、檢查約束與目標分區完全一致
CREATE TABLE StagingTable (...) ON PartitionScheme(PartitionColumn);
-- 2. 切換分區至暫存表 (瞬間,僅更新元資料)
ALTER TABLE BigTable SWITCH PARTITION 5 TO StagingTable;
-- 3. 刪除暫存表 (或歸檔)
DROP TABLE StagingTable; -- 或 TRUNCATE 保留結構供歸檔
DECLARE @BatchSize INT = 5000, @RowsAffected INT = 1;
WHILE @RowsAffected > 0
BEGIN
DELETE TOP (@BatchSize) FROM BigTable
WHERE Time < '2006-03-10';
SET @RowsAffected = @@ROWCOUNT;
-- 可選:CHECKPOINT (SIMPLE 模式) 或 BACKUP LOG (FULL 模式) 釋放日誌空間
WAITFOR DELAY '00:00:00.1'; -- 降低鎖爭用
END;
配合 TOP (N) 分批、頻繁備份日誌/檢查點,控制單次交易大小與日誌增長。