One script that checks the whole server and returns a single list, worst first: CRITICAL (fix now), WARNING (look soon), OK and INFO. It covers backups, CHECKDB, disk space, failed jobs, waits, memory, tempdb, settings and more. Health Check 01 to 12 show the detail behind each line. Needs SQL Server 2016 SP2 or later. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.
/*
00_Health_Check.sql - is this SQL Server running well? Clive Roberts DBA, 8 October 2026
Read-only: changes nothing. Run it on the server (F5). One list comes back, worst first:
CRITICAL = fix now, WARNING = look at it soon, OK = fine, INFO = for the record.
The scripts 01-12 in this folder show the detail behind each area. SQL Server 2016 SP2 or later.
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE [master];
DECLARE @r TABLE (area nvarchar(30), [check] nvarchar(120), status nvarchar(10), detail nvarchar(4000));
DECLARE @now datetime = GETDATE();
-- Server --------------------------------------------------------------------------------------------
INSERT @r SELECT N'Server', N'Version', N'INFO',
CONVERT(nvarchar(30), SERVERPROPERTY('ProductVersion')) + N' ' + CONVERT(nvarchar(60), SERVERPROPERTY('Edition'))
+ N', up ' + CONVERT(nvarchar(10), DATEDIFF(DAY, sqlserver_start_time, @now)) + N' day(s) since '
+ CONVERT(nvarchar(20), sqlserver_start_time, 120)
FROM sys.dm_os_sys_info;
INSERT @r SELECT N'Server', N'SQL Server Agent running',
CASE WHEN EXISTS (SELECT 1 FROM sys.dm_server_services WHERE servicename LIKE N'SQL Server Agent%' AND status_desc = N'Running')
THEN N'OK' ELSE N'CRITICAL' END,
ISNULL((SELECT TOP (1) status_desc FROM sys.dm_server_services WHERE servicename LIKE N'SQL Server Agent%'), N'not found');
INSERT @r SELECT N'Server', N'CPU use, last 30 minutes',
CASE WHEN AVG(sql_cpu) >= 90 THEN N'CRITICAL' WHEN AVG(sql_cpu) >= 75 THEN N'WARNING' ELSE N'OK' END,
N'SQL Server average ' + CONVERT(nvarchar(10), AVG(sql_cpu)) + N'%, peak ' + CONVERT(nvarchar(10), MAX(sql_cpu))
+ N'%; everything else on the box average ' + CONVERT(nvarchar(10), AVG(100 - idle - sql_cpu)) + N'%'
FROM (SELECT TOP (30)
x.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu,
x.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS idle
FROM (SELECT CONVERT(xml, record) AS x, [timestamp] FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%<SystemHealth>%') AS rb
ORDER BY [timestamp] DESC) AS c
HAVING COUNT(*) > 0;
INSERT @r SELECT N'Server', N'Memory pressure (page life expectancy)',
CASE WHEN cntr_value < 300 THEN N'WARNING' ELSE N'OK' END,
CONVERT(nvarchar(20), cntr_value) + N' seconds (under 300 means pages are being thrown out of memory quickly)'
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Page life expectancy' AND [object_name] LIKE N'%Buffer Manager%';
INSERT @r SELECT N'Server', N'Max server memory set',
CASE WHEN CONVERT(bigint, value_in_use) = 2147483647 THEN N'WARNING' ELSE N'OK' END,
CONVERT(nvarchar(20), value_in_use) + N' MB of ' + CONVERT(nvarchar(20), (SELECT physical_memory_kb / 1024 FROM sys.dm_os_sys_info)) + N' MB'
FROM sys.configurations WHERE name = N'max server memory (MB)';
INSERT @r SELECT N'Server', N'MAXDOP / cost threshold',
CASE WHEN (SELECT cpu_count FROM sys.dm_os_sys_info) > 8 AND MAX(CASE WHEN name = N'max degree of parallelism' THEN CONVERT(int, value_in_use) END) = 0
OR MAX(CASE WHEN name = N'cost threshold for parallelism' THEN CONVERT(int, value_in_use) END) = 5
THEN N'WARNING' ELSE N'OK' END,
N'MAXDOP ' + CONVERT(nvarchar(10), MAX(CASE WHEN name = N'max degree of parallelism' THEN CONVERT(int, value_in_use) END))
+ N', cost threshold ' + CONVERT(nvarchar(10), MAX(CASE WHEN name = N'cost threshold for parallelism' THEN CONVERT(int, value_in_use) END))
+ N', ' + CONVERT(nvarchar(10), (SELECT cpu_count FROM sys.dm_os_sys_info)) + N' CPUs'
FROM sys.configurations WHERE name IN (N'max degree of parallelism', N'cost threshold for parallelism');
-- Disks ---------------------------------------------------------------------------------------------
INSERT @r SELECT DISTINCT N'Disks', N'Free space on ' + ISNULL(vs.volume_mount_point, ISNULL(NULLIF(vs.logical_volume_name, N''), N'data drive')),
CASE WHEN vs.available_bytes * 100.0 / vs.total_bytes < 5 THEN N'CRITICAL'
WHEN vs.available_bytes * 100.0 / vs.total_bytes < 10 THEN N'WARNING' ELSE N'OK' END,
CONVERT(nvarchar(20), CONVERT(decimal(10,1), vs.available_bytes / 1073741824.0)) + N' GB free of '
+ CONVERT(nvarchar(20), CONVERT(decimal(10,1), vs.total_bytes / 1073741824.0)) + N' GB ('
+ CONVERT(nvarchar(10), CONVERT(decimal(5,1), vs.available_bytes * 100.0 / vs.total_bytes)) + N'%)'
FROM sys.master_files AS mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs;
-- Databases -----------------------------------------------------------------------------------------
INSERT @r SELECT N'Databases', N'Not online: ' + name, N'CRITICAL', state_desc + CASE WHEN is_read_only = 1 THEN N', read-only' ELSE N'' END
FROM sys.databases WHERE state_desc <> N'ONLINE';
INSERT @r SELECT N'Databases', N'Settings: ' + name, N'WARNING',
STUFF(CASE WHEN is_auto_close_on = 1 THEN N', auto close is ON' ELSE N'' END
+ CASE WHEN is_auto_shrink_on = 1 THEN N', auto shrink is ON' ELSE N'' END
+ CASE WHEN page_verify_option_desc <> N'CHECKSUM' THEN N', page verify is ' + page_verify_option_desc ELSE N'' END, 1, 2, N'')
FROM sys.databases
WHERE database_id <> 2 AND (is_auto_close_on = 1 OR is_auto_shrink_on = 1 OR page_verify_option_desc <> N'CHECKSUM');
INSERT @r SELECT N'Databases', N'Suspect pages', CASE WHEN COUNT(*) > 0 THEN N'CRITICAL' ELSE N'OK' END,
CONVERT(nvarchar(10), COUNT(*)) + N' page(s) in msdb.dbo.suspect_pages (possible corruption - see 09_Integrity.sql)'
FROM msdb.dbo.suspect_pages;
INSERT @r SELECT N'Databases', N'Log almost full: ' + RTRIM(instance_name),
CASE WHEN cntr_value >= 90 THEN N'CRITICAL' ELSE N'WARNING' END,
CONVERT(nvarchar(10), cntr_value) + N'% used, waiting on ' + ISNULL((SELECT log_reuse_wait_desc FROM sys.databases WHERE name = RTRIM(pc.instance_name)), N'?')
FROM sys.dm_os_performance_counters AS pc
WHERE counter_name = N'Percent Log Used' AND instance_name NOT IN (N'_Total', N'mssqlsystemresource')
AND (cntr_value >= 90 OR (cntr_value >= 75 AND EXISTS (SELECT 1 FROM sys.databases WHERE name = RTRIM(pc.instance_name)
AND log_reuse_wait_desc NOT IN (N'NOTHING', N'CHECKPOINT'))));
INSERT @r SELECT N'Databases', N'Too many VLFs: ' + d.name, N'WARNING',
CONVERT(nvarchar(10), COUNT(*)) + N' virtual log files (over 1000 slows start-up and restores; grow the log in bigger steps)'
FROM sys.databases AS d CROSS APPLY sys.dm_db_log_info(d.database_id) AS li
WHERE d.state_desc = N'ONLINE'
GROUP BY d.name HAVING COUNT(*) > 1000;
-- Backups -------------------------------------------------------------------------------------------
;WITH b AS (
SELECT d.name, d.recovery_model_desc,
MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full,
MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs ON bs.database_name = d.name
WHERE d.database_id <> 2 AND d.state_desc = N'ONLINE' AND d.source_database_id IS NULL
GROUP BY d.name, d.recovery_model_desc)
INSERT @r
SELECT N'Backups', N'Full backup: ' + name,
CASE WHEN last_full IS NULL THEN N'CRITICAL' WHEN last_full < DATEADD(DAY, -7, @now) THEN N'CRITICAL'
WHEN last_full < DATEADD(DAY, -1, @now) THEN N'WARNING' ELSE N'OK' END,
ISNULL(N'last ' + CONVERT(nvarchar(20), last_full, 120), N'never backed up')
FROM b
WHERE last_full IS NULL OR last_full < DATEADD(DAY, -1, @now)
UNION ALL
SELECT N'Backups', N'Log backup: ' + name,
CASE WHEN last_log IS NULL OR last_log < DATEADD(HOUR, -4, @now) THEN N'CRITICAL' ELSE N'WARNING' END,
N'FULL recovery, last log backup ' + ISNULL(CONVERT(nvarchar(20), last_log, 120), N'never') + N' - the log keeps growing until it is backed up'
FROM b
WHERE recovery_model_desc = N'FULL' AND name NOT IN (N'model') AND (last_log IS NULL OR last_log < DATEADD(HOUR, -1, @now));
IF NOT EXISTS (SELECT 1 FROM @r WHERE area = N'Backups')
INSERT @r VALUES (N'Backups', N'All databases backed up', N'OK', N'full in the last 24 hours, log in the last hour where needed');
-- Integrity -----------------------------------------------------------------------------------------
INSERT @r SELECT N'Integrity', N'CHECKDB: ' + name,
CASE WHEN last_good IS NULL OR last_good < DATEADD(DAY, -14, @now) THEN N'CRITICAL' ELSE N'WARNING' END,
N'last clean DBCC CHECKDB ' + ISNULL(CONVERT(nvarchar(20), last_good, 120), N'never')
FROM (SELECT name, CONVERT(datetime, DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime')) AS last_good
FROM sys.databases WHERE database_id <> 2 AND state_desc = N'ONLINE') AS c
WHERE last_good IS NULL OR last_good < DATEADD(DAY, -7, @now) OR last_good = '19000101';
IF NOT EXISTS (SELECT 1 FROM @r WHERE area = N'Integrity')
INSERT @r VALUES (N'Integrity', N'CHECKDB on every database', N'OK', N'all checked clean in the last 7 days');
-- SQL Agent -----------------------------------------------------------------------------------------
INSERT @r SELECT N'Agent jobs', N'Failed: ' + j.name, N'WARNING',
CONVERT(nvarchar(10), COUNT(*)) + N' failure(s) in the last 24 hours, last at '
+ CONVERT(nvarchar(20), MAX(msdb.dbo.agent_datetime(h.run_date, h.run_time)), 120)
FROM msdb.dbo.sysjobhistory AS h JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.step_id = 0 AND h.run_status = 0 AND msdb.dbo.agent_datetime(h.run_date, h.run_time) > DATEADD(DAY, -1, @now)
GROUP BY j.name;
INSERT @r SELECT N'Agent jobs', N'Running over 4 hours: ' + j.name, N'WARNING',
N'started ' + CONVERT(nvarchar(20), ja.start_execution_date, 120)
FROM msdb.dbo.sysjobactivity AS ja JOIN msdb.dbo.sysjobs AS j ON j.job_id = ja.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
AND ja.start_execution_date < DATEADD(HOUR, -4, @now);
IF NOT EXISTS (SELECT 1 FROM @r WHERE area = N'Agent jobs')
INSERT @r VALUES (N'Agent jobs', N'Jobs', N'OK', N'no failures in the last 24 hours, nothing stuck');
INSERT @r SELECT N'Agent jobs', N'Database Mail', CASE WHEN COUNT(*) > 0 THEN N'WARNING' ELSE N'OK' END,
CONVERT(nvarchar(10), COUNT(*)) + N' email(s) failed to send in the last 24 hours'
FROM msdb.dbo.sysmail_faileditems WHERE send_request_date > DATEADD(DAY, -1, @now);
-- Activity ------------------------------------------------------------------------------------------
INSERT @r SELECT N'Activity', N'Blocking now', CASE WHEN COUNT(*) > 0 THEN N'WARNING' ELSE N'OK' END,
CONVERT(nvarchar(10), COUNT(*)) + N' session(s) blocked for over 30 seconds (see 07_Activity_and_Blocking.sql)'
FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 AND wait_time > 30000;
-- Error log -----------------------------------------------------------------------------------------
DECLARE @log TABLE (LogDate datetime, ProcessInfo nvarchar(100), [Text] nvarchar(max));
BEGIN TRY
INSERT @log EXEC master.dbo.xp_readerrorlog 0, 1, N'Severity: ';
END TRY BEGIN CATCH END CATCH;
INSERT @r SELECT N'Error log', N'Errors (severity 17+) in the last 24 hours',
CASE WHEN COUNT(*) > 0 THEN N'WARNING' ELSE N'OK' END,
CONVERT(nvarchar(10), COUNT(*)) + N' (see 08_Error_Log.sql)'
FROM @log
WHERE LogDate > DATEADD(DAY, -1, @now)
AND TRY_CONVERT(int, SUBSTRING([Text], CHARINDEX(N'Severity: ', [Text]) + 10, 2)) >= 17;
-- tempdb --------------------------------------------------------------------------------------------
INSERT @r SELECT N'tempdb', N'Data files',
CASE WHEN COUNT(*) < CASE WHEN (SELECT cpu_count FROM sys.dm_os_sys_info) > 8 THEN 8 ELSE (SELECT cpu_count FROM sys.dm_os_sys_info) END
OR COUNT(DISTINCT size) > 1 OR MAX(is_percent_growth + 0) = 1 THEN N'WARNING' ELSE N'OK' END,
CONVERT(nvarchar(10), COUNT(*)) + N' data file(s) for ' + CONVERT(nvarchar(10), (SELECT cpu_count FROM sys.dm_os_sys_info)) + N' CPUs'
+ CASE WHEN COUNT(DISTINCT size) > 1 THEN N', sizes differ' ELSE N', all the same size' END
FROM sys.master_files WHERE database_id = 2 AND type = 0;
-- High availability ---------------------------------------------------------------------------------
IF SERVERPROPERTY('IsHadrEnabled') = 1
INSERT @r SELECT N'Availability groups', N'Replica ' + ar.replica_server_name + N' (' + ag.name + N')',
CASE WHEN rs.synchronization_health_desc = N'HEALTHY' THEN N'OK' ELSE N'CRITICAL' END,
rs.role_desc + N', ' + rs.synchronization_health_desc + N', ' + ISNULL(rs.connected_state_desc, N'')
FROM sys.dm_hadr_availability_replica_states AS rs
JOIN sys.availability_replicas AS ar ON ar.replica_id = rs.replica_id
JOIN sys.availability_groups AS ag ON ag.group_id = rs.group_id;
-- Security ------------------------------------------------------------------------------------------
INSERT @r SELECT N'Security', N'sa login', CASE WHEN is_disabled = 0 THEN N'WARNING' ELSE N'OK' END,
CASE WHEN is_disabled = 0 THEN N'enabled - disable or rename it' ELSE N'disabled' END
FROM sys.server_principals WHERE sid = 0x01;
INSERT @r SELECT N'Security', N'sysadmin members', N'INFO',
CONVERT(nvarchar(10), COUNT(*)) + N': '
+ STUFF((SELECT N', ' + p2.name FROM sys.server_role_members AS rm2 JOIN sys.server_principals AS p2 ON p2.principal_id = rm2.member_principal_id
WHERE rm2.role_principal_id = SUSER_ID(N'sysadmin') AND p2.name NOT LIKE N'NT SERVICE\%' AND p2.name NOT LIKE N'##%'
ORDER BY p2.name FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N'')
FROM sys.server_role_members AS rm JOIN sys.server_principals AS p ON p.principal_id = rm.member_principal_id
WHERE rm.role_principal_id = SUSER_ID(N'sysadmin') AND p.name NOT LIKE N'NT SERVICE\%' AND p.name NOT LIKE N'##%';
-- Result --------------------------------------------------------------------------------------------
SELECT area, [check], status, detail
FROM @r
ORDER BY CASE status WHEN N'CRITICAL' THEN 1 WHEN N'WARNING' THEN 2 WHEN N'OK' THEN 3 ELSE 4 END, area, [check];
DECLARE @crit int = (SELECT COUNT(*) FROM @r WHERE status = N'CRITICAL'), @warn int = (SELECT COUNT(*) FROM @r WHERE status = N'WARNING');
PRINT N'Health check of ' + @@SERVERNAME + N' at ' + CONVERT(nvarchar(20), @now, 120) + N': '
+ CONVERT(nvarchar(10), @crit) + N' critical, ' + CONVERT(nvarchar(10), @warn) + N' warning(s).';