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 result
SELECT
j.name AS job_name,
j.enabled,
CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS last_run_status,
msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS last_run_time,
-- Duration in HH:MM:SS
RIGHT('0' + CAST(jh.run_duration / 10000 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST((jh.run_duration % 10000) / 100 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(jh.run_duration % 100 AS VARCHAR), 2) AS duration_hhmmss,
jh.message
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
AND jh.instance_id = (
SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
WHERE job_id = j.job_id AND step_id = 0
)
ORDER BY last_run_time 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 = Failed
AND msdb.dbo.agent_datetime(jh.run_date, jh.run_time) >= DATEADD(HOUR, -24, GETDATE())
ORDER BY 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 = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
AND ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL;
2. Error Log
-- Read current SQL Server error log
-- Parameters: (log file #, log type, search str1, search str2, start, end, sort order)
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, 'DESC';
-- Filter to errors only (severity 17+)
EXEC xp_readerrorlog 0, 1, 'Error', NULL, NULL, NULL, 'DESC';
-- Check for specific timeframe
DECLARE @start DATETIME = DATEADD(HOUR, -4, GETDATE());
DECLARE @end DATETIME = GETDATE();
EXEC xp_readerrorlog 0, 1, NULL, NULL, @start, @end, 'DESC';
-- Check previous log file (log rotates on restart)
EXEC xp_readerrorlog 1, 1, NULL, NULL, NULL, NULL, 'DESC';
-- How many error log files exist
EXEC xp_enumerrorlogs;
3. Database Health
-- Status and log reuse reason for all databases
SELECT
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 compatibility
FROM sys.databases
ORDER BY 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 space
SELECT
DB_NAME(mf.database_id) AS database_name,
mf.name AS logical_name,
mf.physical_name,
mf.type_desc,
mf.size * 8 / 1024 AS allocated_mb,
(mf.size * 8 / 1024) -
(FILEPROPERTY(mf.name, 'SpaceUsed') * 8 / 1024) AS free_mb,
FILEPROPERTY(mf.name, 'SpaceUsed') * 8 / 1024 AS used_mb,
CASE mf.is_percent_growth
WHEN 1 THEN CAST(mf.growth AS VARCHAR) + '%'
ELSE CAST(mf.growth * 8 / 1024 AS VARCHAR) + ' MB'
END AS auto_growth
FROM sys.master_files mf
ORDER BY database_name, mf.type_desc;
5. Blocking Analysis
Script: ../scripts/blocking-analysis.sql
Quick Blocking Check
-- Sessions that are blocked and what's blocking them
SELECT
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time / 1000.0 AS wait_time_s,
r.total_elapsed_time / 1000.0 AS elapsed_s,
SUBSTRING(t.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset WHEN -1 THEN 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 > 0
ORDER BY 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
LEFT JOIN 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 blocked
SELECT DISTINCT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
)
AND s.session_id NOT IN (
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 buffer
SELECT
xdr.value('@timestamp', 'DATETIME2') AS deadlock_time,
xdr.query('.') AS deadlock_graph_xml
FROM (
SELECT CAST(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)
ORDER BY 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 minutes
SELECT
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 at
JOIN 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
LEFT JOIN 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()) > 5
ORDER BY 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.