您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 實用運維技巧集錦（一）：掛起操作、收縮壓縮、用戶遷移、備份驗證、損壞修復、記錄檔清理

編輯：關於SqlServer

一、掛起操作快速解決

安裝 SQL Server / SP 時提示「有掛起的安裝操作，需重啟」，重啟無效：

REGEDIT → HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
刪除 PendingFileRenameOperations 值 (建議先匯出備份)

二、資料庫收縮與索引重建標準流程

-- 1. 重建索引 (消除碎片、更新統計資訊)
DBCC REINDEX('TableName', 'PK_IndexName', 90);  -- 指定填充因子 90%
-- 或全表
DBCC REINDEX('TableName', '', 90);

-- 2. 索引重組 (輕量、線上)
DBCC INDEXDEFRAG(DB_ID(), OBJECT_ID('TableName'), IndexId);

-- 3. 收縮資料檔案 (資料頁移動、極易產生碎片)
DBCC SHRINKDATABASE(DBName, 10);  -- 目標剩餘 10% 空間
DBCC SHRINKFILE(LogicalDataFileName, TargetSizeMB);

-- 4. 收縮記錄檔 (主要目的：釋放記錄檔空間)
DBCC SHRINKFILE(LogicalLogFileName, TargetSizeMB);

黃金法則：收縮資料檔案**必須**後接索引重建，否則碎片嚴重反而傷效能。記錄檔收縮相對安全。

三、壓縮資料庫 (資料壓縮 2008+ / 頁面/列壓縮)

-- 2005 僅支援壓縮備份 (WITH COMPRESSION)
-- 2008+ 支援資料壓縮
ALTER TABLE dbo.BigTable REBUILD WITH (DATA_COMPRESSION = PAGE);
ALTER INDEX ALL ON dbo.BigTable REBUILD WITH (DATA_COMPRESSION = ROW);

四、用戶權限遷移：孤立用戶修復

-- 將資料庫用戶 'newname' 映射至伺服器登入 'oldname'
EXEC sp_change_users_login 'Update_One', 'newname', 'oldname';
-- 或自動修復所有孤立用戶
EXEC sp_change_users_login 'Auto_Fix', 'username', NULL, 'password';

五、備份集驗證：還原前必檢

RESTORE VERIFYONLY FROM DISK = 'E:\Backup\dvbbs.bak';
-- 僅驗證備份集完整性 (檢查和、頁面結構)，不還原資料
-- 成功返回：「備份集上的檢查和驗證成功。」

六、資料庫損壞修復：DBCC CHECKDB 完整參數指南

-- 1. 進入單用戶模式 (阻塞其他連線)
ALTER DATABASE [dvbbs] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- 2. 執行修復 (三種等級由輕到重)
-- 輕量：僅修復非叢集索引附加鍵，無資料遺失風險
DBCC CHECKDB('dvbbs', REPAIR_FAST) WITH TABLOCK;

-- 中等：含 REPAIR_FAST + 重建索引，無資料遺失風險
DBCC CHECKDB('dvbbs', REPAIR_REBUILD) WITH TABLOCK;

-- 重度：含上述 + 頁面/配置修復、**可能遺失資料**
DBCC CHECKDB('dvbbs', REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK;

-- 3. 恢復多用戶模式
ALTER DATABASE [dvbbs] SET MULTI_USER;
GO

修復等級選擇決策樹

先執行 `DBCC CHECKDB('db') NO_INFOMSGS` 純檢查，記錄錯誤類型
非叢集索引錯誤 → `REPAIR_FAST`
叢集索引/堆結構錯誤 → `REPAIR_REBUILD`
資料頁損壞、配置錯誤 → **備份優先**；無備份才 `REPAIR_ALLOW_DATA_LOSS`
修復後**必須**重新 `DBCC CHECKDB` 驗證 + 完整備份

快速物理檢查 (僅檢查頁面結構，忽略邏輯)

DBCC CHECKDB('dvbbs') WITH NO_INFOMSGS, PHYSICAL_ONLY;

七、記錄檔清理兩大經典方法

方法一：簡單模式切換 + 收縮 (適合非生產/可接受時間點恢復)

SSMS → 資料庫屬性 → 選項 → 復原模式：**簡單** → 確定
右鍵資料庫 → 所有工作 → 收縮資料庫 → 確定 (預設值即可)
收縮完成 → 復原模式改回**完整** (生產環境必須)

風險：簡單模式下無法時間點恢復，僅能恢復至最近完整/差異備份。

方法二：手動截斷迴圈 (保持完整復原模式，精確控制目標大小)

SET NOCOUNT ON;
DECLARE
    @LogicalFileName SYSNAME = 'dvbbs_log',  -- 邏輯記錄檔名
    @MaxMinutes INT = 10,                    -- 最大等待分鐘
    @NewSizeMB INT = 100;                    -- 目標大小 (MB)

USE dvbbs;
DECLARE @OriginalSize INT = (SELECT size * 8 / 1024 FROM sys.database_files WHERE name = @LogicalFileName);
DECLARE @StartTime DATETIME = GETDATE();
DECLARE @TruncLog NVARCHAR(MAX) = N'BACKUP LOG ' + QUOTENAME(DB_NAME()) + N' WITH TRUNCATE_ONLY';

-- 迴圈：備份截斷 → 收縮 → 產生虛擬交易填滿記錄 → 重複
WHILE @MaxMinutes > DATEDIFF(MI, @StartTime, GETDATE())
  AND (SELECT size * 8 / 1024 FROM sys.database_files WHERE name = @LogicalFileName) > @NewSizeMB
BEGIN
    EXEC (@TruncLog);  -- 截斷非作用中虛擬記錄
    DBCC SHRINKFILE(@LogicalFileName, @NewSizeMB);
    -- 產生虛擬交易推進 VLF
    DECLARE @i INT = 0;
    WHILE @i < 100
    BEGIN
        INSERT INTO dbo.DummyTrans VALUES (REPLICATE('X', 8000));
        DELETE FROM dbo.DummyTrans;
        SET @i += 1;
    END
END

SELECT 'Final Log Size (MB)=' + CAST((size * 8 / 1024) AS VARCHAR(10))
FROM sys.database_files WHERE name = @LogicalFileName;

DROP TABLE IF EXISTS dbo.DummyTrans;
SET NOCOUNT OFF;

下一集將分享：重複資料清除四大模式、資料表擁有者批次變更、循環寫入測試資料、更多 `DBCC` 實用命令、效能監控 DMV 查詢、自動化維護計畫設計。