SQL Server DBA 實戰筆記：連線數、版本、還原、複寫、鎖死鎖、日誌清理總彙整

核心監控查詢

• 目前連線數：SELECT * FROM master..sysprocesses
• 最大允許連線數：SELECT @@MAX_CONNECTIONS (預設 32767)，可在伺服器屬性調整。
• 版本資訊：SELECT @@VERSION 或 SELECT SERVERPROPERTY('ProductLevel')。

資料庫還原實戰：單一表從備份還原

1. 將 .bak 複製到另一台機器，建立同名資料庫並還原（選擇「強制還原」）。
2. 邏輯檔名 (it_Products_Data, it_Products_Log) 必須保留，物理路徑可重新指定。
3. 若還原到不同資料庫名 (test)：同樣強制還原，邏輯檔名不變，物理檔名改為 test_Data.mdf, test_Log.ldf。

複寫同步新增欄位/檢視

1. 複寫監視器 → 發佈伺服器 → 選發佈 → 屬性 → 篩選欄位。
2. 勾選新增欄位 → 啟動 快照代理程式 → 啟動 散發代理程式。

效能瓶頸排查清單 (ASP.NET + SQL Server)

1. 升級記憶體、調高 max server memory。
2. SQL Profiler 捕捉阻塞與長查詢。
3. sp_configure 'locks', 20000 增加鎖資源 (需重啟)。
4. 避免 DBCC、SERIALIZABLE 隔離等級、應用程式碼中的 UPDLOCK / HOLDLOCK。
5. 鎖定阻塞資源表 → 分析阻塞程式碼。
6. 查詢加 NOLOCK / 設定 READ UNCOMMITTED 降低封鎖。
7. 安裝 SP2、所有 SELECT 後補 COMMIT (釋放共享鎖)。
8. sp_lock 檢視 locktype 含 -blk 表有阻塞。
9. master..syslocks 結合 sysdatabases 查阻塞表：
   SELECT b.name dbname, a.id table_id, page, spid FROM syslocks a JOIN sysdatabases b ON a.dbid=b.dbid WHERE a.type & 256 = 256

鎖類型對照 (syslocks.type)

死鎖監控預存程序 P_lockInfo

功能：列出死鎖鏈 (阻塞者/受害者)、顯示執行中 SQL (DBCC INPUTBUFFER)、可選自動 KILL 死鎖 SPID。完整程式碼見原文。

交易日誌清理正規做法

-- 簡單復原模式
BACKUP LOG dbname WITH NO_LOG;   -- 截斷日誌
DBCC SHRINKFILE (dbname_log, 5); -- 壓縮至 5MB

-- 完整復原模式：需定期 BACKUP LOG TO DISK 才能截斷

注意：DUMP TRANSACTION 為 Sybase 相容語法，正式環境請用 BACKUP LOG。

複寫訂閱失效急救

重新啟動 散發代理程式、記錄讀取代理程式、合併代理程式 (視複寫類型)。