SQL Server monitoring — SQL Agent jobs, error log, database health, blocking chains, deadlock analysis, long-running transactions, and disk space.
SQL Server Monitoring
Use this skill to check SQL Server health, investigate blocking or deadlocks, monitor SQL Agent jobs, and review the error log.
1. SQL Agent Jobs
Check Job Status
-- All jobs and their last run resultSELECT
j.name AS job_name,
j.enabled,
CASE jh.run_status
WHEN0THEN'Failed'WHEN1THEN'Succeeded'WHEN2THEN'Retry'WHEN3THEN'Cancelled'WHEN4THEN'In Progress'ENDAS last_run_status,
msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS last_run_time,
-- Duration in HH:MM:SSRIGHT( (jh.run_duration ), )
( ((jh.run_duration ) ), )
( (jh.run_duration ), ) duration_hhmmss,
jh.message
msdb.dbo.sysjobs j
msdb.dbo.sysjobhistory jh j.job_id jh.job_id
jh.instance_id (
(instance_id) msdb.dbo.sysjobhistory
job_id j.job_id step_id
)
last_run_time ;
'0'
+
CAST
/
10000
AS
VARCHAR
2
+
':'
+
RIGHT
'0'
+
CAST
%
10000
/
100
AS
VARCHAR
2
+
':'
+
RIGHT
'0'
+
CAST
%
100
AS
VARCHAR
2
AS
FROM
LEFT
JOIN
ON
=
AND
=
SELECT
MAX
FROM
WHERE
=
AND
=
0
ORDER
BY
DESC
Find Failed Jobs in Last 24 Hours
SELECT
j.name AS job_name,
msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS run_time,
jh.step_id, jh.step_name,
jh.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE jh.run_status =0-- 0 = FailedAND msdb.dbo.agent_datetime(jh.run_date, jh.run_time) >= DATEADD(HOUR, -24, GETDATE())
ORDERBY run_time DESC;
Currently Running Jobs
SELECT
j.name AS job_name,
ja.start_execution_date,
DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS running_minutes,
ja.last_executed_step_id,
ja.last_executed_step_date
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECTMAX(session_id) FROM msdb.dbo.syssessions)
AND ja.start_execution_date ISNOT NULLAND ja.stop_execution_date ISNULL;
-- Status and log reuse reason for all databasesSELECT
name,
state_desc, -- Should be ONLINE; SUSPECT/EMERGENCY = problem
recovery_model_desc,
log_reuse_wait_desc, -- Why log space can't be reused (see below)
is_read_only,
is_auto_close_on, -- Should be OFF in production
is_auto_shrink_on, -- Should be OFF in production
compatibility_level -- SQL Server version compatibilityFROM sys.databases
ORDERBY name;
log_reuse_wait_desc values:
Value
Meaning
Fix
NOTHING
Log can be reused — healthy
—
LOG_BACKUP
Waiting for a log backup (FULL recovery mode)
Take a transaction log backup
CHECKPOINT
Waiting for checkpoint
Run CHECKPOINT or wait
ACTIVE_TRANSACTION
Long-running open transaction
Find and close the transaction
DATABASE_MIRRORING
Mirroring partner is behind
Check mirroring latency
REPLICATION
Replication not read
Check distributor
AVAILABILITY_REPLICA
AG secondary is behind
Check AG health
4. Disk Space
-- Data and log file sizes and free spaceSELECT
DB_NAME(mf.database_id) AS database_name,
mf.name AS logical_name,
mf.physical_name,
mf.type_desc,
mf.size *8/1024AS allocated_mb,
(mf.size *8/1024) -
(FILEPROPERTY(mf.name, 'SpaceUsed') *8/1024) AS free_mb,
FILEPROPERTY(mf.name, 'SpaceUsed') *8/1024AS used_mb,
CASE mf.is_percent_growth
WHEN1THENCAST(mf.growth ASVARCHAR) +'%'ELSECAST(mf.growth *8/1024ASVARCHAR) +' MB'ENDAS auto_growth
FROM sys.master_files mf
ORDERBY database_name, mf.type_desc;
5. Blocking Analysis
Script:../scripts/blocking-analysis.sql
Quick Blocking Check
-- Sessions that are blocked and what's blocking themSELECT
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time /1000.0AS wait_time_s,
r.total_elapsed_time /1000.0AS elapsed_s,
SUBSTRING(t.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset WHEN-1THEN DATALENGTH(t.text)
ELSE r.statement_end_offset END- r.statement_start_offset)/2)+1) AS blocked_query,
s.login_name,
s.host_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id >0ORDERBY r.wait_time DESC;
Find the Head Blocker
-- The session blocking all others (head of the chain)SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status,
t.text AS current_or_last_query
FROM sys.dm_exec_sessions s
LEFTJOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, s.sql_handle)) t
WHERE s.session_id IN (
-- Sessions that ARE blocking someone but are NOT themselves blockedSELECTDISTINCT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id >0
)
AND s.session_id NOTIN (
SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id >0
);
⚠️ Using KILL:
-- KILL should only be used after business confirmation that the blocking transaction-- can be safely terminated. Killing a session rolls back all open transactions.-- Never execute KILL autonomously.-- KILL 57; -- Replace 57 with actual session_id
6. Deadlock Detection
Read from System Health Session
SQL Server captures deadlock graphs automatically in the system_health extended events session.
-- Read deadlock events from system_health ring bufferSELECT
xdr.value('@timestamp', 'DATETIME2') AS deadlock_time,
xdr.query('.') AS deadlock_graph_xml
FROM (
SELECTCAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
WHERE s.name ='system_health'AND t.target_name ='ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xdt(xdr)
ORDERBY deadlock_time DESC;
Click the deadlock_graph_xml result in SSMS to view the graphical deadlock graph.
Read Deadlock Graph
In the deadlock graph:
Ovals = transactions/processes
Rectangles = resources (locks)
Arrows = "this process owns this lock" and "this process is waiting for this lock"
The process marked with the X was chosen as the deadlock victim (killed by SQL Server)
Common deadlock patterns:
Update order deadlock — Two sessions update the same two tables in different order. Fix: standardize update order.
Reader-writer deadlock — Reader holds shared lock, writer needs exclusive. Fix: add indexes to reduce scan time; use RCSI.
Foreign key deadlock — Parent-child insert/delete in different order. Fix: index foreign key columns on child table.
7. Long-Running Transactions
-- Transactions open longer than 5 minutesSELECT
s.session_id,
s.login_name,
s.host_name,
at.transaction_begin_time,
DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS open_minutes,
at.transaction_type,
at.transaction_state,
t.text AS last_query
FROM sys.dm_tran_active_transactions atJOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
LEFTJOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, s.sql_handle)) t
WHERE DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) >5ORDERBY open_minutes DESC;
Long-running transactions cause:
Log space not being reused (log_reuse_wait_desc = ACTIVE_TRANSACTION)
Version store bloat in TempDB (if RCSI is enabled)
Blocking other sessions
Fix: investigate whether the transaction is in an application that forgot to commit, or whether a stored procedure is running longer than expected.
