核心維護任務與 T-SQL 實現
1. 索引維護(重建/重組/統計更新)
-- 基於碎片率動態決定
DECLARE @SchemaName SYSNAME, @TableName SYSNAME, @IndexName SYSNAME, @Frag FLOAT
DECLARE cur CURSOR FOR
SELECT OBJECT_SCHEMA_NAME(ips.OBJECT_ID), OBJECT_NAME(ips.OBJECT_ID), i.name, ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.OBJECT_ID = i.OBJECT_ID AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10 AND i.index_id > 0
OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @Frag
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(MAX) = N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
IF @Frag >= 30
SET @SQL += N' REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);'
ELSE
SET @SQL += N' REORGANIZE;'
EXEC sp_executesql @SQL
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @Frag
END
CLOSE cur; DEALLOCATE cur
-- 更新統計信息
EXEC sp_updatestats; -- 或針對性 UPDATE STATISTICS
2. 完整性檢查
-- 快速檢查(僅頁面一致性)
DBCC CHECKDB WITH PHYSICAL_ONLY, NO_INFOMSGS;
-- 完整檢查(維護窗口執行)
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;
3. 備份自動化腳本模板
DECLARE @Path NVARCHAR(256) = N'D:\Backup\',
@DBName SYSNAME = DB_NAME(),
@DateStr NVARCHAR(20) = CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', ''),
@File NVARCHAR(512) = @Path + @DBName + '_FULL_' + @DateStr + '.bak'
BACKUP DATABASE @DBName TO DISK = @File
WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;
4. 日志空間監控與縮減(謹慎)
-- 查看日志使用率
DBCC SQLPERF(LOGSPACE);
-- 縮減日志(僅簡單恢復模式或備份後)
USE [YourDB]
DBCC SHRINKFILE (N'YourDB_log', 1024); -- 目標 1GB
5. 過期/歸檔數據清理(分批刪除避免長事務)
DECLARE @Rows INT = 1, @BatchSize INT = 5000
WHILE @Rows > 0
BEGIN
DELETE TOP (@BatchSize) FROM LargeTable
WHERE CreatedDate < DATEADD(MONTH, -12, GETDATE())
SET @Rows = @@ROWCOUNT
WAITFOR DELAY '00:00:00.1' -- 釋放鎖
END
自動化部署建議
- SQL Server Agent 作業:按計劃執行上述腳本,配置失敗通知(Database Mail)。
- Ola Hallengren 腳本:業界標準維護解決方案(備份、完整性、索引、統計),開源、參數化、生產級,強烈推薦直接部署而非重造輪子。
- 監控指標:備份成功率、完整性檢查耗時/錯誤、索引碎片率趨勢、日志空間使用率、磁盤剩餘空間。
避坑指南
- 切勿在業務高峰期執行
REBUILD(即使 ONLINE=ON 也有鎖升級風險)。
SHRINKFILE 會產生碎片、阻塞、觸發自動增長,極度謹慎,優先擴容磁盤。
- 完整性檢查發現錯誤時,先備份再修復,嚴禁直接
REPAIR_ALLOW_DATA_LOSS。
- 維護作業日誌保留至少 30 天,便於審計與趨勢分析。