SQL Server DBA 必備實戰技巧匯總：安裝、維護、備份、修復與優化

一、 環境與安裝疑難雜症

解決「掛起的檔案操作」導致無法安裝/修補

註冊表殘留 PendingFileRenameOperations 導致安裝程式誤判需重啟。

-- 位置：HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
-- 動作：刪除 PendingFileRenameOperations 值，然後重新執行安裝

SQL Server 2005/2008 SP 安裝 TCP/IP 監聽失敗

SP3 安裝時若未啟用 Named Pipes 且順序不在第一，會報 Specified SQL Server not found。

• 客戶端/伺服器網路工具 → 啟用 Named Pipes 並移至第一順位。
• 註冊表 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo 確認 DSQUERY=DBNETLIB。
• 停止 MSSQL 服務後再執行安裝。

二、 資料庫維護核心指令

索引重建與碎片整理

DBCC REINDEX ('TableName', 'PK_Index', 90);  -- 重建指定索引，填充因子 90%
DBCC INDEXDEFRAG ('DBName', 'TableName', 'PK_Index'); -- 線上整理 (較輕量)
-- SQL 2005+ 建議使用 ALTER INDEX ... REBUILD / REORGANIZE

收縮資料庫與記錄檔 (謹慎使用)

-- 收縮整個資料庫 (含資料與記錄)
DBCC SHRINKDATABASE (DBName);
-- 僅收縮記錄檔 (指定目標大小 MB)
DBCC SHRINKFILE (N'DBName_Log', 100);

警告：頻繁收縮會產生大量碎片，導致效能下降。建議僅在大量刪除資料或異常增長後單次執行，並配合索引重建。

修復資料庫狀態 (Suspect/單一用戶模式)

ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- 允許資料遺失的修復 (最後手段)
DBCC CHECKDB ('DBName', REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK;
GO
ALTER DATABASE [DBName] SET MULTI_USER;
GO

REPAIR_ALLOW_DATA_LOSS 可能刪除損壞頁面導致資料遺失，務必先備份。REPAIR_REBUILD 無資料遺失風險但修復能力較弱。

驗證備份檔案完整性

RESTORE VERIFYONLY FROM DISK = 'E:\Backup\DB.bak';

三、 記錄檔暴增緊急處理 (兩大經典方案)

方案一：GUI 操作 (適合新手)

1. 屬性 → 選項 → 恢復模式改為「簡單」。
2. 右鍵資料庫 → 所有工作 → 收縮資料庫 → 確定。
3. 恢復模式改回「完整」，並立即執行完整備份 + 記錄備份啟動新鏈。

方案二：T-SQL 精準控制 (適合自動化/大記錄檔)

USE [TargetDB];
DECLARE @LogicalName SYSNAME = 'TargetDB_Log', @TargetSizeMB INT = 100, @MaxMin INT = 10;
DECLARE @OrigSize INT = (SELECT size FROM sys.database_files WHERE name = @LogicalName);
CREATE TABLE #Dummy (col CHAR(8000) NOT NULL);
DECLARE @sql NVARCHAR(MAX) = N'BACKUP LOG [' + DB_NAME() + '] WITH TRUNCATE_ONLY'; -- 簡單模式下有效
-- 完整模式下請改為：BACKUP LOG [DB] TO DISK='NUL:' (測試用) 或正常備份後 SHRINKFILE
DBCC SHRINKFILE (@LogicalName, @TargetSizeMB);
-- 迴圈填充記錄強制截斷 (舊版本技巧，新版本建議直接正規備份)
-- ... 略 ...
DROP TABLE #Dummy;

現代最佳實踐：完整模式下，執行 BACKUP LOG [DB] TO DISK='Path\Log.trn' 後緊接 DBCC SHRINKFILE (LogFile, TargetSize)。

四、 刪除重複資料經典模式

完全重複行 (保留一筆)

SELECT DISTINCT * INTO #Tmp FROM SourceTable;
TRUNCATE TABLE SourceTable;
INSERT INTO SourceTable SELECT * FROM #Tmp;
DROP TABLE #Tmp;
-- 根治：加上 PRIMARY KEY 或 UNIQUE INDEX

關鍵欄位重複 (保留第一筆，如 Name+Address)

SELECT IDENTITY(INT,1,1) AS RowID, * INTO #Tmp FROM SourceTable;
SELECT MIN(RowID) AS RowID INTO #Keep FROM #Tmp GROUP BY Name, Address;
SELECT * FROM #Tmp WHERE RowID IN (SELECT RowID FROM #Keep);

五、 物件擁有者變更 (解決還原後 Schema 錯誤)

-- 單一資料表
EXEC sp_changeobjectowner 'OldUser.TableName', 'dbo';

-- 批次變更存儲過程
CREATE PROCEDURE dbo.BatchChangeOwner @OldOwner NVARCHAR(128), @NewOwner NVARCHAR(128)
AS
BEGIN
    DECLARE @Name NVARCHAR(128), @Owner NVARCHAR(128), @FullName NVARCHAR(256);
    DECLARE cur CURSOR FOR SELECT name, USER_NAME(uid) FROM sysobjects WHERE USER_NAME(uid) = @OldOwner;
    OPEN cur; FETCH NEXT FROM cur INTO @Name, @Owner;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @FullName = @OldOwner + '.' + @Name;
        EXEC sp_changeobjectowner @FullName, @NewOwner;
        FETCH NEXT FROM cur INTO @Name, @Owner;
    END
    CLOSE cur; DEALLOCATE cur;
END

六、 無記錄檔 (LDF 遺損) 災難恢復實戰

核心原理：利用 EMERGENCY MODE + DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS) 重建記錄檔，再匯出資料。

1. 停止服務，備份 MDF 檔案。
2. 建立同名空資料庫，停止服務，用原 MDF 覆蓋新 MDF。
3. 啟動服務，資料庫顯示「置疑」。
4. 執行緊急修復腳本：

USE MASTER;
GO
EXEC sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;
GO
UPDATE sysdatabases SET status = 32768 WHERE name = 'SuspectDB'; -- 設為 Emergency Mode
GO
EXEC sp_dboption 'SuspectDB', 'single user', 'true';
GO
DBCC CHECKDB ('SuspectDB', REPAIR_ALLOW_DATA_LOSS); -- 重建 Log
GO
EXEC sp_resetstatus 'SuspectDB'; -- 重置狀態
GO
EXEC sp_configure 'allow updates', 0; RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_dboption 'SuspectDB', 'single user', 'false';
GO

修復成功後，資料庫可讀但不可寫 (Bypass Recovery)。需建立新資料庫，用 DTS/BCP 匯出資料、腳本建立物件，完成遷移。

七、 版本與補丁查詢

SELECT @@VERSION;
-- 輸出範例: Microsoft SQL Server 2008 (SP3) - 10.0.5500 ...
-- 版本對照: 8.00.194 (RTM), 8.00.760 (SP3), 9.00.4035 (2005 SP3), 10.0.1600 (2008 RTM)

八、 圖形化備份/還原/自動維護計畫精簡步驟

• 備份：企業管理器 → 工具 → 備份資料庫 → 選「完全」 → 指定 .bak 路徑。
• 還原：新建同名空資料庫 → 工具 → 還原資料庫 → 從裝置選 .bak → 選項頁勾選「強制還原」與「保持可運作」 → 修正還原路徑 (MDF/LDF 實體檔名建議改為新資料庫名)。
• 自動備份：工具 → 資料庫維護計畫 → 選 DB → 設定每日備份 → 指定目錄 (建議每 DB 獨立子目錄) → 刪除 7 天前備份 → 啟用 SQL Server Agent 服務。

九、 存儲過程優化黃金法則 (摘要)

• 跨庫查詢：建立 View 代替 DB.dbo.Table，方便 sp_depends 追蹤。
• 避免大事務：慎用 HOLDLOCK，縮短鎖持有時間。
• 拒絕游標：>1 萬行必改寫；游標中避免 JOIN。
• WHERE 條件順序：遵循索引順序，範圍大→小；避免函數運算在「=」左側。
• EXISTS > COUNT(*)：判斷存在性用 EXISTS。
• 索引維護：OLTP 表索引 ≤ 6 個；定期 REBUILD + UPDATE STATISTICS。
• TempDB 保護：避免頻繁建/刪臨時表；大量寫入用 SELECT INTO；用畢 TRUNCATE -> DROP。