SQL Server DBA 必備：30 條高頻管理 T-SQL 腳本速查手冊

資訊查詢類

1. 版本與建置資訊

SELECT @@VERSION AS VersionInfo;
-- 或
SELECT SERVERPROPERTY('ProductVersion') AS Ver,
       SERVERPROPERTY('ProductLevel') AS Level,
       SERVERPROPERTY('Edition') AS Edition;

2. 作業系統層級資訊

EXEC master..xp_msver;

3. 啟動參數與追蹤旗標

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure;
-- 啟動參數
SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%Parameters%';

4. 執行個體啟動時間

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
-- 相容舊版
SELECT login_time FROM master..sysprocesses WHERE spid = 1;

5. 伺服器/實例名稱

SELECT @@SERVERNAME AS ServerName, @@SERVICENAME AS InstanceName;

6. 所有資料庫大小與狀態

EXEC sp_helpdb;
-- 更詳細
SELECT name, state_desc, recovery_model_desc,
       size_mb = SUM(size)*8/1024.0
FROM sys.master_files
GROUP BY name, state_desc, recovery_model_desc;

7. 重新命名資料庫

ALTER DATABASE [OldName] MODIFY NAME = [NewName];
-- 或舊版
EXEC sp_renamedb 'OldName', 'NewName';

安全性與登入管理

8. 所有伺服器登入與預設資料庫

EXEC sp_helplogins;

9. 伺服器角色成員

EXEC sp_helpsrvrolemember;

10. 修復孤立使用者 (SID 不匹配)

-- 方法 1：sp_change_users_login (舊版)
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'NewPassword';

-- 方法 2：ALTER USER (SQL 2005+)
ALTER USER UserName WITH LOGIN = LoginName;

11. 變更物件擁有者

ALTER SCHEMA NewSchema TRANSFER OldSchema.ObjectName;
-- 舊版
EXEC sp_changeobjectowner 'OldSchema.ObjectName', 'NewOwner';

12. 連結伺服器登入對應

EXEC sp_helplinkedsrvlogin;

空間與物件分析

13. 物件空間使用量

EXEC sp_spaceused 'Schema.TableName';
-- 前 N 大表
EXEC sp_topTables @numResults = 20;

14. 索引資訊

EXEC sp_helpindex 'Schema.TableName';
-- 詳細版
EXEC sp_helpindexex 'Schema.TableName'; -- 需自建或第三方

15. 約束資訊

EXEC sp_helpconstraint 'Schema.TableName';

程式碼與中繼資料

16. 所有預存程序/函數清單

USE TargetDB;
EXEC sp_stored_procedures;

17. 檢視程式碼本體

EXEC sp_helptext 'Schema.ProcName';
-- 或
SELECT OBJECT_DEFINITION(OBJECT_ID('Schema.ProcName'));

18. 建立加密程序 / 解密

CREATE PROC Schema.ProcName WITH ENCRYPTION AS SELECT 1;
-- 解密需第三方工具或 DAC 連線查詢 sys.sysobjvalues

19. 搜尋包含特定字串的物件

SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%SearchString%';
-- 現代版
SELECT name, type_desc FROM sys.sql_modules WHERE definition LIKE '%SearchString%';

工作階段與效能監控

20. 所有工作階段

EXEC sp_who;
EXEC sp_who 'active';

21. 鎖定資訊

EXEC sp_lock;
-- 詳細死鎖分析
SELECT * FROM sys.dm_tran_locks;

22. 進程正在執行的 SQL (spid)

DBCC INPUTBUFFER (55); -- 替換實際 SPID
-- 改良版：直接顯示 SQL 文本
EXEC sp_who3; -- 需自建

23. 死鎖偵測

-- 開啟追蹤旗標 1222/1204 記錄至 Errorlog
DBCC TRACEON (1222, 1204, -1);
-- 或使用 Extended Events (system_health 預設已捕捉 deadlock_graph)

維護與修復

24. 簡單復原模式縮減記錄檔

BACKUP LOG [DBName] WITH NO_LOG; -- SQL 2005/2008 已移除
-- 現代做法
ALTER DATABASE [DBName] SET RECOVERY SIMPLE;
DBCC SHRINKFILE ([DBName]_Log, 100); -- 目標 MB
ALTER DATABASE [DBName] SET RECOVERY FULL; -- 還原完整模式並立即完整備份

25. 效能分析開關

SET STATISTICS TIME ON;   -- CPU/Elapsed
SET STATISTICS IO ON;     -- 邏輯/物理讀寫
SET STATISTICS PROFILE ON; -- 執行計畫 + 實際統計

26. 圖形/文字執行計畫

-- SSMS: Ctrl+L (估計) / Ctrl+M (實際)
-- 文字版
SET SHOWPLAN_ALL ON;  -- 不執行
SET SHOWPLAN_TEXT ON;
SET STATISTICS XML ON; -- XML 格式，可另存為 .sqlplan

27. 不一致錯誤修復流程 (3624/823/824/825)

-- 1. 單一使用者模式隔離
ALTER DATABASE [ErrDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- 2. 修復表級
DBCC CHECKTABLE ('Schema.Table') WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- 3. 或修復庫級 (擇一)
DBCC CHECKDB ('ErrDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB ('ErrDB', REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- 4. 恢復多使用者
ALTER DATABASE [ErrDB] SET MULTI_USER;
-- 5. 修復後必須完整備份

三種 REPAIR 差異：

• REPAIR_FAST：輕量、無資料遺失（修補非叢集索引鍵）。
• REPAIR_REBUILD：含 REPAIR_FAST + 重建索引、無資料遺失。
• REPAIR_ALLOW_DATA_LOSS：刪除損壞頁/資料列，**可能遺失資料**，最後手段。

進階腳本：自動化巡檢範例

-- 每日巡檢：備份狀態、磁碟空間、長時間運行查詢、封鎖鏈
-- 部署為 Agent 作業，結果寫入中央巡檢庫或發送 HTML 郵件