SQL Server 資料庫健康情形檢查腳本 (Database Health Monitor T-SQL Script)

2021-07-27

筆記檢查資料庫健康情形的 T-SQL 腳本。

SQL Server Logo

說明

隨著對於資料庫的管理面向更加豐富，腳本也會隨著成長 😆

監測指標

指標 說明
資料庫數量 每一個資料庫的用途、管理者都應該被明確定義。因此資料庫的總數至關重要。
特殊權限登入數 檢查是否有不該出現的超高權限登入
作業數量 盤點啟用、未啟用的作業數量
磁碟可用空間 確保資料庫的壓縮、備份有正常。也避免儲存空間不足的問題。
資料庫可用記憶體 因為 SQL 會佔著記憶體不放，參考意義有限。

Registered Server (已註冊的伺服器)

驚喜地發現藉由 SSMS 的 Registered Server，可以讓 Script 分別向各 Registered Server 去執行查詢，如此一來就不需要透過 Connect 來切換 Server 以進行查詢，距離自動化查詢又更靠近了一步 😀

Script Evolution

啟用「查詢」中的 SQLCMD 模式，並於 connect 中進切換 ServerName 即可依序查詢各資料庫的健康資訊。

:connect serverName

將 Script 調整，結合 TSQL 將查詢結果會成 Single Row 的方式易於檢視與整理資料。

/* 資料庫數量 */
DECLARE @dbnumbers INT
SELECT @dbnumbers = count(*) from sys.databases

/* 特殊權限登入數 */
DECLARE @sausers INT
SELECT @sausers = count(c.name)
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
  ON a.principal_id = b.role_principal_id 
    AND a.type = 'R' AND a.name ='sysadmin'
INNER JOIN sys.server_principals c
  ON b.member_principal_id = c.principal_id

/* 作業數量 */
DECLARE @jobDisable INT
DECLARE @jobEnable INT

SELECT @jobEnable = 
  JobNumbers FROM (SELECT enabled as 'enableJobs', count(*) as JobNumbers
FROM msdb.dbo.sysjobs
GROUP BY enabled) AS JOBS
Where JOBS.enableJobs = 1

SELECT @jobDisable = 
  JobNumbers FROM (SELECT enabled as 'enableJobs', count(*) as JobNumbers
FROM msdb.dbo.sysjobs
GROUP BY enabled) AS JOBS
Where JOBS.enableJobs = 0

/* 資料庫磁碟可用空間 */
DECLARE @C_Drive INT
DECLARE @D_Drive INT
DECLARE @E_Drive INT
DECLARE @Table TABLE(
        DriveName VARCHAR(MAX),
        Storage INT
)

-- 將 sp_who2 寫入 Temp Table
INSERT INTO @Table EXEC MASTER..xp_fixeddrives

SELECT @C_Drive = Storage FROM @Table Where DriveName = 'C'
SELECT @D_Drive = Storage FROM @Table Where DriveName = 'D'
SELECT @E_Drive = Storage FROM @Table Where DriveName = 'E'

/* 資料庫可用實體記憶體 */
DECLARE @totalMemory INT
DECLARE @availableMemory INT

SELECT @totalMemory = total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory 
SELECT @availableMemory = available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory 

----------------------------------------

DECLARE @dataDrive INT
SET @dataDrive = @E_Drive

IF @E_Drive IS NULL
	SET @dataDrive = @D_Drive

DECLARE @serverName VARCHAR(30)
SET @serverName = Upper(Convert(varchar(max),ServerProperty('MachineName')))

SELECT 
	@dbnumbers AS 'DBNumbers', 
	@sausers AS 'SA_Users', 	
	@jobEnable AS 'jobsEnable', 
	--@jobDisable AS 'jobsDisable',
	@C_Drive AS 'Sys_Drive',
	@dataDrive AS 'Data_Drive',
	@availableMemory AS '可用記憶體',
	--@totalMemory AS '總記憶體',
	@serverName 'ServerName'

Script Origin

/* 資料庫數量 */
SELECT count(*) as DBNumbers from sys.databases

/* 特殊權限登入數 */
SELECT count(c.name) as 'SA_Users'
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
  ON a.principal_id = b.role_principal_id 
    AND a.type = 'R' AND a.name ='sysadmin'
INNER JOIN sys.server_principals c
  ON b.member_principal_id = c.principal_id

/* 作業數量 */
SELECT enabled as 'enableJobs', count(*) as count
FROM msdb.dbo.sysjobs job
GROUP BY enabled

/* 資料庫磁碟可用空間 */
EXEC MASTER..xp_fixeddrives

/* 資料庫可用實體記憶體 */
SELECT 
  total_physical_memory_kb/1024 as [總實體記憶體(MB)]
  ,available_physical_memory_kb/1024 as [可用實體記憶體(MB)]
FROM sys.dm_os_sys_memory

更多詳細資訊

List All SQL Server Jobs Steps

SELECT
  job.job_id,
  notify_level_email,
  name,
  enabled,
  description,
  step_name,
  command,
  server,
  database_name
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobsteps steps        
  ON job.job_id = steps.job_id

Database Memory Information

SELECT 
  total_physical_memory_kb/1024 as [總實體記憶體(MB)]
  ,available_physical_memory_kb/1024 as [可用實體記憶體(MB)]
  ,total_page_file_kb/1024 as [總分頁檔(MB)]
  ,available_page_file_kb /1024 as [可用分頁檔(MB)]
  ,system_cache_kb/1024 as [系統快取記憶體(MB)]
  ,system_high_memory_signal_state
  ,system_low_memory_signal_state
  ,system_memory_state_desc 
FROM sys.dm_os_sys_memory
#Database #SQLServer