SQL Server 2005 資料維護實務指南 (2)：索引重建、統計更新、歷程清除、備份與清除維護任務進階

續接上集，深入剖析索引重建進階選項、統計更新細節、歷程清除與備份任務關鍵配置、清除維護任務實務參數。

3.4 重新建立索引任務進階選項

可用空間策略

• 使用預設填充因子：刪除並重建索引，套用索引定義的填充因子 (或 0 = 預設)。
• 自訂填充因子百分比：指定新填充因子 (0-100)，重建時套用。值越大 = 預留空間越多 = 未來分頁分裂越少 = 索引體積越大。

高級選項

• 排序結果存放於 tempdb (`SORT_IN_TEMPDB = ON`)：中繼排序資料寫入 tempdb，減少使用者資料庫記錄檔壓力、加速排序 (若 tempdb 獨立磁碟)。
• 保持索引線上 (`ONLINE = ON`)：重建期間允許基礎表查詢與 DML，**Enterprise 版專屬**，需額外臨時空間儲存版本記錄。
• 忽略重複鍵 (`IGNORE_DUP_KEY = OFF`)：唯一索引重建時遇到重複值報錯 (預設) vs 忽略並警告。

實戰 T-SQL 範例 (AdventureWorks Sales.SalesOrderDetail 所有索引線上重建、tempdb 排序)

USE [AdventureWorks];
GO
ALTER INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail] 
REBUILD WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
    SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON);
GO
ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] 
REBUILD WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
    SORT_IN_TEMPDB = ON, ONLINE = ON);
GO
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] 
REBUILD WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
    SORT_IN_TEMPDB = ON, ONLINE = ON);
GO

3.5 更新統計資料任務完整選項

**建議**：大型資料表維護窗口充足時用 `FULLSCAN`；窗口緊湊或極大表用 `SAMPLE 10 PERCENT` 或 `SAMPLE 10000 ROWS`。

T-SQL 等價

USE [AdventureWorks];
GO
UPDATE STATISTICS [Sales].[SalesOrderDetail] WITH FULLSCAN;

3.6 清除歷程記錄任務參數化

• 刪除類型：備份/還原歷程、Agent 作業歷程、維護計畫歷程 (可多選)。
• 保留閾值：指定「保留超過 X 天/週/月/年」的記錄，系統自動換算為日期時間。
• 報表輸出：完成後可寫入文字檔或郵件寄送操作員 (需設定 Database Mail)。

T-SQL 實作 (清除 4 週前所有歷程)

DECLARE @dt DATETIME = '2007-10-21T09:26:24';
EXEC msdb.dbo.sp_delete_backuphistory @dt;
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = '2007-10-21T09:26:24';
GO
EXECUTE msdb..sp_maintplan_delete_log NULL, NULL, '2007-10-21T09:26:24';

3.7 執行 SQL Server Agent 作業任務

• 在設計師「可用 Agent 作業」頁籤勾選，或 T-SQL 指定 `@job_id`。
• 常見用途：維護窗口結束後觸發自訂 SSIS 套件 (匯出報表、同步資料倉儲、發送通知)。

T-SQL

EXEC msdb.dbo.sp_start_job @job_id = N'35eca119-28a6-4a29-994b-0680ce73f1f3';

3.8 備份資料庫任務關鍵配置與避坑指南

擴充選項詳細

三種備份類型在維護計畫中的對應

• **完整備份**：基準點，含所有資料頁。
• **差異備份**：上次完整備份後變更的頁面 (DCM 追蹤)，體積小、速度快。
• **記錄備份**：交易記錄區間，**僅限完整/大量記錄復原模式**。

⚠️ 關鍵避坑：記錄傳送環境禁用維護計畫記錄備份

記錄傳送內建獨立記錄備份/複製/還原作業。若維護計畫再建立記錄備份，將產生**兩組不連續的記錄備份鏈**。還原時需**兩組鏈完整合併**才能恢復至故障點，否則中斷點無法跨越。解決方案：啟用記錄傳送後，維護計畫**僅保留完整/差異備份任務**，記錄備份完全交給記錄傳送作業。

3.9 清除維護任務參數

• 刪除類型：資料庫備份檔 / 維護計畫文字報表。
• 檔案路徑：單一檔案或資料夾。
• 遞迴與副檔名：搜尋資料夾含一級子資料夾，依副檔名 (`.bak`、`.trn`、`.txt`) 批次刪除。
• 保留時間：時/天/週/月/年，超過者刪除。

下集將示範精靈建立完整維護計畫、任務排序原則、排程配置、報表與通知設定。