SQL Server DBA 日常維護核心腳本大全：完整性檢查、備份、索引、統計、文件管理

一、數據庫完整性檢查

-- 基礎檢查
DBCC CHECKDB (YourDB);

-- 加速選項：使用 TABLOCK (需獨佔/低併發窗口)
DBCC CHECKDB (YourDB) WITH TABLOCK, ALL_ERRORMSGS, NO_INFOMSGS;

-- 僅檢查特定表
DBCC CHECKTABLE ('YourTable') WITH TABLOCK;

-- 檢查系統表
DBCC CHECKCATALOG;

二、數據庫屬性修改

-- 重命名
ALTER DATABASE OldName MODIFY NAME = NewName;

-- 恢復模式
ALTER DATABASE YourDB SET RECOVERY FULL;      -- 完整
ALTER DATABASE YourDB SET RECOVERY SIMPLE;    -- 簡單
ALTER DATABASE YourDB SET RECOVERY BULK_LOGGED; -- 大容量日志

-- 訪問模式
ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;   -- 單用戶 (強制回滾)
ALTER DATABASE YourDB SET RESTRICTED_USER;                        -- 受限用戶 (sysadmin/db_owner)
ALTER DATABASE YourDB SET MULTI_USER;                             -- 多用戶 (正常)

-- 自動屬性
ALTER DATABASE YourDB SET AUTO_CLOSE OFF;
ALTER DATABASE YourDB SET AUTO_SHRINK OFF;  -- 生產環境強烈建議 OFF
ALTER DATABASE YourDB SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS ON;

三、文件與文件組管理

-- 1. 添加文件組
ALTER DATABASE YourDB ADD FILEGROUP FG_Data_01;

-- 2. 添加數據文件到文件組
ALTER DATABASE YourDB ADD FILE (
    NAME = FG_Data_01_File1,
    FILENAME = 'E:\MSSQL\Data\FG_Data_01_File1.ndf',
    SIZE = 5120MB, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB
) TO FILEGROUP FG_Data_01;

-- 3. 添加日志文件
ALTER DATABASE YourDB ADD LOG FILE (
    NAME = Log_File2,
    FILENAME = 'F:\MSSQL\Log\Log_File2.ldf',
    SIZE = 2048MB, MAXSIZE = 10240MB, FILEGROWTH = 256MB
);

-- 4. 修改文件大小/增長/最大值
ALTER DATABASE YourDB MODIFY FILE (
    NAME = FG_Data_01_File1,
    SIZE = 10240MB,           -- 必須大於當前大小
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024MB       -- 固定增長優於百分比
);

-- 5. 修改文件邏輯名稱
ALTER DATABASE YourDB MODIFY FILE (NAME = OldLogicalName, NEWNAME = NewLogicalName);

-- 6. 移動文件 (需離線數據庫)
-- 步驟：CHECKPOINT → SET OFFLINE → 物理移動文件 → MODIFY FILE 指定新路徑 → SET ONLINE

-- 7. 設置默認文件組
ALTER DATABASE YourDB MODIFY FILEGROUP FG_Data_01 DEFAULT;

-- 8. 文件組只讀/讀寫
ALTER DATABASE YourDB MODIFY FILEGROUP FG_Archive READ_ONLY;
ALTER DATABASE YourDB MODIFY FILEGROUP FG_Archive READ_WRITE;

四、收縮操作 (謹慎使用！)

-- 收縮數據庫 (指定目標空閒百分比)
DBCC SHRINKDATABASE (YourDB, 10);           -- 保留 10% 空閒
DBCC SHRINKDATABASE (YourDB, 10, NOTRUNCATE); -- 僅移動頁不釋放磁盤
DBCC SHRINKDATABASE (YourDB, 10, TRUNCATEONLY); -- 僅釋放文件尾部空閒

-- 收縮具體文件 (指定目標大小 MB)
DBCC SHRINKFILE (DataFile_LogicalName, 5120);  -- 壓縮至 5GB
DBCC SHRINKFILE (LogFile_LogicalName, 1024);   -- 壓縮日志

-- 清空文件以便刪除 (數據遷移到同文件組其他文件)
DBCC SHRINKFILE (FileToRemove, EMPTYFILE);

-- 刪除文件
ALTER DATABASE YourDB REMOVE FILE FileToRemove;

-- 刪除文件組 (必須先刪除所有文件)
ALTER DATABASE YourDB REMOVE FILEGROUP FG_Old;

五、索引維護

-- 1. 重新組織索引 (碎片 5-30%，在線、輕量)
ALTER INDEX [IX_YourIndex] ON [dbo].[YourTable] REORGANIZE WITH (LOB_COMPACTION = ON);

-- 2. 重建索引 (碎片 >30%，離線/在線)
ALTER INDEX [IX_YourIndex] ON [dbo].[YourTable] REBUILD 
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    ONLINE = ON,              -- Enterprise/2016 SP1+ Standard 支持
    SORT_IN_TEMPDB = ON,      -- 利用 TempDB 排序，減少主文件日志
    FILLFACTOR = 90,          -- 預留 10% 空間減少頁分裂
    DATA_COMPRESSION = PAGE   -- 頁壓縮 (Enterprise)
);

-- 3. 全表所有索引重建
ALTER INDEX ALL ON [dbo].[YourTable] REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

六、統計信息更新

-- 單個統計對象
UPDATE STATISTICS YourTable (StatName) WITH FULLSCAN;

-- 全表所有統計
UPDATE STATISTICS YourTable WITH FULLSCAN;

-- 采樣更新 (大表)
UPDATE STATISTICS YourTable WITH SAMPLE 20 PERCENT;

-- 使用最近采樣率
UPDATE STATISTICS YourTable WITH RESAMPLE;

-- 禁用自動更新 (特殊場景)
UPDATE STATISTICS YourTable WITH NORECOMPUTE;

七、SQL Server Agent 作業管理

-- 啟用作業
EXEC msdb.dbo.sp_start_job @job_name = 'JobName';

-- 停用作業
EXEC msdb.dbo.sp_stop_job @job_name = 'JobName';

-- 查看作業歷史
SELECT TOP 50 j.name, h.run_date, h.run_time, h.run_status, h.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE j.name = 'JobName'
ORDER BY h.instance_id DESC;

八、備份腳本模板 (完整/差異/日志)

-- 完整備份
BACKUP DATABASE YourDB TO DISK = 'G:\Backup\YourDB_Full_20240115.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 5;

-- 差異備份
BACKUP DATABASE YourDB TO DISK = 'G:\Backup\YourDB_Diff_20240115_1400.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 5;

-- 日志備份
BACKUP LOG YourDB TO DISK = 'G:\Backup\YourDB_Log_20240115_1430.trn'
WITH COMPRESSION, STATS = 5;

-- 驗證備份
RESTORE VERIFYONLY FROM DISK = 'G:\Backup\YourDB_Full_20240115.bak';

九、實用查詢：空間使用、碎片度、阻塞、昂貴查詢

-- 1. 所有數據庫大小
EXEC sp_helpdb;

-- 2. 當前數據庫文件大小
SELECT name, size*8/1024.0 AS SizeMB, FILEPROPERTY(name, 'SpaceUsed')*8/1024.0 AS UsedMB
FROM sys.database_files;

-- 3. 表空間使用 (準確)
EXEC sp_spaceused 'YourTable', @updateusage = 'true';

-- 4. 索引碎片度
SELECT OBJECT_NAME(object_id) AS TableName, index_id, index_type_desc, 
       avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5 AND page_count > 100
ORDER BY avg_fragmentation_in_percent DESC;

-- 5. 當前阻塞鏈
SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource, 
       TEXT = SUBSTRING(st.text, (r.statement_start_offset/2)+1, 
       ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1)
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE blocking_session_id <> 0;

-- 6. 高耗資源查詢 (需 Query Store 或 緩存計劃)
SELECT TOP 20 qs.total_worker_time/qs.execution_count AS AvgCPU, 
       qs.total_logical_reads/qs.execution_count AS AvgReads,
       qs.execution_count, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
       ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY AvgCPU DESC;