筆記檢查資料庫健康情形的 T-SQL 腳本。
說明
隨著對於資料庫的管理面向更加豐富,腳本也會隨著成長 😆
監測指標
指標 | 說明 |
---|---|
資料庫數量 | 每一個資料庫的用途、管理者都應該被明確定義。因此資料庫的總數至關重要。 |
特殊權限登入數 | 檢查是否有不該出現的超高權限登入 |
作業數量 | 盤點啟用、未啟用的作業數量 |
磁碟可用空間 | 確保資料庫的壓縮、備份有正常。也避免儲存空間不足的問題。 |
資料庫可用記憶體 | 因為 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