SQL Server 實用運維技巧大全：從安裝疑難到資料修復的完整工具箱

一、安裝/補丁掛起操作徹底解決

REGEDIT → HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
刪除 PendingFileRenameOperations (先匯出備份)
-- 若多處殘留，全域搜尋刪除

二、索引維護與收縮標準作業程序 (SOP)

-- 1. 診斷
DBCC SHOWCONTIG(OBJECT_ID('Table')) WITH TABLERESULTS, ALL_INDEXES;

-- 2. 重建 (離線、更新統計資訊、可指定填充因子)
DBCC DBREINDEX('Table', 'PK_Index', 90);  -- 單索引
DBCC DBREINDEX('Table', '', 90);          -- 全表

-- 3. 重組 (線上、單執行緒、輕量)
DBCC INDEXDEFRAG(DB_ID(), OBJECT_ID('Table'), IndexId);

-- 4. 收縮資料檔 (謹慎！產生碎片)
DBCC SHRINKDATABASE(DBName, 10);  -- 目標 10% 餘裕
DBCC SHRINKFILE(DataLogicalName, TargetMB);

-- 5. 收縮記錄檔 (主要用途)
DBCC SHRINKFILE(LogLogicalName, TargetMB);

-- 6. 壓縮 (2008+)
ALTER TABLE T REBUILD WITH (DATA_COMPRESSION = PAGE);
ALTER INDEX ALL ON T REBUILD WITH (DATA_COMPRESSION = ROW);

三、孤立用戶修復與權限遷移

-- 單一用戶
EXEC sp_change_users_login 'Update_One', 'NewUser', 'OldLogin';

-- 批次修復所有孤立用戶
DECLARE @User SYSNAME;
DECLARE Cur CURSOR FOR
    SELECT name FROM sys.database_principals
    WHERE type IN ('S','U') AND sid NOT IN (SELECT sid FROM sys.server_principals);
OPEN Cur;
FETCH NEXT FROM Cur INTO @User;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_change_users_login 'Auto_Fix', @User, NULL, 'TempP@ssw0rd!';
    FETCH NEXT FROM Cur INTO @User;
END
CLOSE Cur; DEALLOCATE Cur;

四、備份驗證與還原測試

-- 快速驗證
RESTORE VERIFYONLY FROM DISK = 'E:\Backup\db.bak';

-- 完整還原測試 (異機驗證)
RESTORE DATABASE db_Test FROM DISK = 'E:\Backup\db.bak'
WITH MOVE 'db_Data' TO 'D:\Data\db_Test.mdf',
     MOVE 'db_Log'  TO 'D:\Log\db_Test.ldf',
     REPLACE, STATS = 10;
-- 驗證後立即 DROP 測試庫

五、資料庫損壞修復：DBCC CHECKDB 實戰決策

-- 階段 1：純檢查 (無修復)
DBCC CHECKDB('db') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;

-- 階段 2：依錯誤類型選擇修復等級
-- 僅非叢集索引錯誤
DBCC CHECKDB('db', REPAIR_FAST) WITH TABLOCK;

-- 含叢集索引/堆結構錯誤
DBCC CHECKDB('db', REPAIR_REBUILD) WITH TABLOCK;

-- 頁面/配置損壞 (最後手段，資料遺失風險)
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB('db', REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK;
ALTER DATABASE db SET MULTI_USER;

六、記錄檔暴漲雙法制勝

法一：簡單模式切換收縮 (非生產/可接受 RPO=備份間隔)

1. 屬性 → 選項 → 復原模式：**簡單**
2. 收縮資料庫/檔案
3. 改回**完整**模式 → **立即完整備份** 重新啟動記錄鏈

法二：手動截斷迴圈 (生產環境保持完整模式)

USE TargetDB;
DECLARE @LogFile SYSNAME = 'TargetDB_log', @TargetMB INT = 500;
DECLARE @SQL NVARCHAR(MAX) = N'BACKUP LOG ' + QUOTENAME(DB_NAME()) + N' WITH TRUNCATE_ONLY';

WHILE (SELECT size*8/1024 FROM sys.database_files WHERE name = @LogFile) > @TargetMB
BEGIN
    EXEC (@SQL);
    DBCC SHRINKFILE(@LogFile, @TargetMB);
    -- 產生虛擬交易推進 VLF
    INSERT INTO DummyTrans VALUES (REPLICATE('X',8000));
    DELETE FROM DummyTrans;
END
DROP TABLE IF EXISTS DummyTrans;

七、重複資料清除四大模式

模式 1：游標逐組刪除 (保留第一筆)

DECLARE @Max INT, @ID INT;
DECLARE Cur CURSOR LOCAL FOR
    SELECT PK, COUNT(*) FROM DupTable GROUP BY PK HAVING COUNT(*) > 1;
OPEN Cur;
FETCH NEXT FROM Cur INTO @ID, @Max;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET ROWCOUNT @Max - 1;
    DELETE FROM DupTable WHERE PK = @ID;
    FETCH NEXT FROM Cur INTO @ID, @Max;
END
CLOSE Cur; DEALLOCATE Cur;
SET ROWCOUNT 0;

模式 2：完全重複列去重 (保留一筆)

SELECT DISTINCT * INTO #Tmp FROM DupTable;
DROP TABLE DupTable;
SELECT * INTO DupTable FROM #Tmp;
DROP TABLE #Tmp;
-- 根治：加唯一索引/主鍵

模式 3：關鍵欄位重複 (保留第一筆，其餘欄位忽略)

SELECT IDENTITY(INT,1,1) AS RN, * INTO #Tmp FROM DupTable;
SELECT MIN(RN) AS RN INTO #Keep FROM #Tmp GROUP BY KeyCol1, KeyCol2;
SELECT * FROM #Tmp WHERE RN IN (SELECT RN FROM #Keep);

模式 4：CTE + ROW_NUMBER (2005+ 推薦，效能最佳)

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY KeyCol1, KeyCol2 ORDER BY PK) AS RN
    FROM DupTable
)
DELETE FROM CTE WHERE RN > 1;

八、資料表擁有者批次變更

-- 單表
EXEC sp_changeobjectowner 'Schema.Table', 'dbo';

-- 批次預存程序
CREATE PROCEDURE dbo.ChangeOwnerBatch
    @OldOwner SYSNAME, @NewOwner SYSNAME
AS
DECLARE @Name SYSNAME, @Owner SYSNAME, @FullName SYSNAME;
DECLARE Cur CURSOR FOR
    SELECT name, USER_NAME(uid) FROM sysobjects WHERE USER_NAME(uid) = @OldOwner ORDER BY name;
OPEN Cur;
FETCH NEXT FROM Cur INTO @Name, @Owner;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FullName = @OldOwner + '.' + RTRIM(@Name);
    EXEC sp_changeobjectowner @FullName, @NewOwner;
    FETCH NEXT FROM Cur INTO @Name, @Owner;
END
CLOSE Cur; DEALLOCATE Cur;
GO

-- 執行
EXEC dbo.ChangeOwnerBatch 'OldUser', 'dbo';

九、循環寫入測試資料範本

DECLARE @i INT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO TestTable (Col1, Col2, Col3)
    VALUES (@i, 'Name_' + CAST(@i AS VARCHAR(10)), DATEADD(DAY, @i % 365, '2020-01-01'));
    SET @i += 1;
    IF @i % 1000 = 0 WAITFOR DELAY '00:00:00.010'; -- 避免記錄檔暴漲
END

十、更多實用 DBCC 命令速查

本工具箱涵蓋 SQL Server 2000/2005/2008 核心運維場景，建議收藏離線備份。生產環境執行任何修復/收縮/清除操作前，**務必先完整備份並驗證可還原**。