萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

使用 T-SQL 進行 SQL 數據庫維護:常用維護腳本與自動化思路

核心維護任務與 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 天,便於審計與趨勢分析。
copyright © 萬盛學電腦網 all rights reserved