SQL Server DBA 常用管理腳本大全 (含版本、大小、鎖、日誌、效能等)

整理 SQL Server 2000/2005/2008 DBA 日常運維最常用的 T-SQL 指令與系統預存程序，涵蓋版本查詢、資料庫大小、用戶權限、鎖死偵測、日誌縮減、效能分析、損壞修復等實戰場景，可直接收藏備查。

1. 基礎資訊查詢

• 版本號：SELECT @@VERSION (對照 SP 版本：8.00.194=RTM, 384=SP1, 532=SP2, 760=SP3, 2039=SP4)
• OS 參數：EXEC master..xp_msver
• 啟動參數：sp_configure
• 啟動時間：SELECT CONVERT(VARCHAR(30), login_time, 120) FROM master..sysprocesses WHERE spid = 1
• 伺服器/實例名：PRINT @@SERVERNAME; PRINT @@SERVICENAME

2. 資料庫與空間管理

• 所有 DB 名稱與大小：sp_helpdb
• 重命名 DB：sp_renamedb 'old', 'new'
• 單一物件大小：sp_spaceused @objname / 更詳細 SP_NChelpindex @objname
• 前 N 大表：sp_toptables (預設 50)
• 索引資訊：sp_helpindex @objname (Clustered 索引物理排序、空間較小；高頻 DML 建議 Non-clustered + 預設 fillfactor)
• 約束資訊：sp_helpconstraint @objname

3. 用戶、登入與權限

• 所有登入：sp_helplogins
• 伺服器角色成員：sp_helpsrvrolemember
• 修復孤立用戶：sp_change_users_login 'Auto_Fix', 'user' 或自訂 fix_orphan_user
• 變更物件擁有者：sp_changeobjectowner 'obj', 'newowner'
• 備份登入腳本：sp_help_revlogin (非內建，需下載微軟腳本)
• 物件級權限：sp_helprotect

4. 連結/遠端伺服器

• 連結伺服器登入：sp_helplinkedsrvlogin
• 遠端登入：sp_helpremotelogin

5. 預存程序與函數

• 列出所有 SP/函數：USE db; sp_stored_procedures
• 檢視原始碼：sp_helptext 'proc_name'
• 搜尋含特定字串的物件：SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%str%'
• 加密建立：CREATE PROC ... WITH ENCRYPTION AS ...；解密可用第三方 sp_decrypt

6. 進程、鎖與阻塞診斷

• 所有進程：sp_who / 僅作用中：sp_who 'active'
• 鎖資訊：sp_lock (spid 1-50 為系統，>50 為用戶)
• 檢視進程執行 SQL：DBCC INPUTBUFFER(spid) / 推薦強化版 sp_who3
• 死鎖偵測：sp_who_lock (本文稍後提供完整建立腳本)

7. 交易記錄維護

-- 查看所有 DB 記錄大小
DBCC SQLPERF(LOGSPACE)

-- 簡單復原模式縮減記錄 (單位 MB)
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE (db_name_log, 5)

8. 效能分析開關

SET STATISTICS TIME ON/OFF
SET STATISTICS IO ON/OFF
-- 圖形化執行計畫: Ctrl+L 或工具列圖示
SET SHOWPLAN_ALL ON/OFF
SET SHOWPLAN_TEXT ON/OFF
SET STATISTICS PROFILE ON/OFF

9. 資料庫不一致修復 (嚴重錯誤 3624)

ALTER DATABASE [db] SET SINGLE_USER
-- 修復單表 (允許資料遺失)
DBCC CHECKTABLE('table', REPAIR_ALLOW_DATA_LOSS)
-- 或修復整庫
DBCC CHECKDB('db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [db] SET MULTI_USER

參數差異：REPAIR_FAST (輕微、無風險) → REPAIR_REBUILD (含重建索引、無遺失) → REPAIR_ALLOW_DATA_LOSS (最徹底、可能遺失資料)。修復後務必立即備份。

10. 實用自訂預存程序：sp_who_lock (阻塞檢查) 與 sp_Kill_lockProcess (殺死阻塞進程)

腳本已內建於本文 <xContent> 中，可直接部署至 master 資料庫使用。核心邏輯：識別阻塞鏈頭 (blocked=0 但被其他 spid blocked)、遞迴列出所有受阻進程、透過 DBCC INPUTBUFFER 取得執行語句、可選參數自動 KILL 阻塞源頭。