SQL Server - Health Check

Technical Architects Zone (TAZ)

SQL Server Daily Health Check Scripts

Uptime, CPU, Memory, Disk, Blocking, Backups, TempDB, Waits & Always On

Introduction

A SQL Server health check should provide a quick picture of the instance before a performance or availability problem becomes a production incident.

For a DBA, this means looking beyond whether SQL Server is simply online. Server uptime, CPU-intensive queries, operating-system memory, disk capacity, database files, transaction logs, blocking, long-running requests, failed jobs, backup health, TempDB, wait statistics, and Availability Group health all provide important pieces of the overall picture.

This page contains practical T-SQL scripts that can be used individually during troubleshooting or together as part of a regular SQL Server morning health check.

Important: A health-check value should always be compared with the normal baseline for that SQL Server. A high number by itself does not always indicate a problem.

Contents

1. SQL Server Uptime 2. CPU and Expensive Queries 3. Memory Health 4. Disk Free Space 5. Database File Size 6. Transaction Log Usage 7. Blocking Sessions 8. Long-Running Queries 9. Deadlock Monitoring 10. Wait Statistics 11. Failed SQL Agent Jobs 12. Backup Health 13. Active Connections 14. TempDB Usage 15. Always On Availability Group Health 16. One-Click Morning Health Check

1. SQL Server Uptime

The SQL Server start time helps identify recent service restarts and provides context when interpreting cumulative DMV information.

SELECT
    sqlserver_start_time AS SQLServerStartTime,
    DATEDIFF(DAY, sqlserver_start_time, GETDATE()) AS UptimeDays
FROM sys.dm_os_sys_info;

A recent SQL Server restart may explain why plan-cache statistics and several cumulative DMV values contain less historical information than expected.

2. CPU and Expensive Queries

For a practical T-SQL health check, start by identifying cached queries that have consumed significant CPU.

SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000.0 AS TotalCPU_ms,
    (qs.total_worker_time / NULLIF(qs.execution_count, 0))
        / 1000.0 AS AvgCPU_ms,
    qs.total_elapsed_time / 1000.0 AS TotalElapsed_ms,
    qs.total_logical_reads,
    qs.last_execution_time,
    SUBSTRING(st.text, 1, 1000) AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

Remember: These values depend on cached plans. A restart, recompilation, plan eviction, or cache clearing can remove or reset the corresponding history.

3. Memory Health

This script provides an operating-system-level memory overview as seen by SQL Server.

SELECT
    total_physical_memory_kb / 1024 AS TotalMemoryMB,
    available_physical_memory_kb / 1024 AS AvailableMemoryMB,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

Low available physical memory should be investigated together with SQL Server memory configuration, operating-system usage, memory grants, paging, and workload behavior.

4. Disk Free Space

EXEC master.dbo.xp_fixeddrives;

Free-space thresholds should be based on expected database growth, autogrowth configuration, backup activity, maintenance operations, and available response time, not only a fixed percentage.

5. Database File Size

SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalName,
    type_desc,
    physical_name,
    CAST(size * 8.0 / 1024 AS decimal(18,2)) AS SizeMB,
    growth,
    is_percent_growth
FROM sys.master_files
ORDER BY
    DatabaseName,
    type_desc,
    name;

File size should be reviewed together with actual space usage, autogrowth, disk capacity, and historical database growth.

6. Transaction Log Usage

For a quick instance-wide transaction log utilization check:

DBCC SQLPERF(LOGSPACE);

Also check why transaction log space cannot currently be reused:

SELECT
    name AS DatabaseName,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
ORDER BY name;

7. Blocking Sessions

SELECT
    r.session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time AS WaitTime_ms,
    r.wait_resource,
    st.text AS QueryText
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

Do not immediately KILL the blocker. First understand the blocking chain, transaction age, workload, application owner, and potential rollback duration.

8. Long-Running Queries

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.start_time,
    r.status,
    r.command,
    r.total_elapsed_time / 1000.0 AS DurationSeconds,
    r.cpu_time / 1000.0 AS CPUSeconds,
    r.logical_reads,
    r.wait_type,
    r.blocking_session_id,
    st.text AS QueryText
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id <> @@SPID
  AND r.total_elapsed_time > 60000
ORDER BY r.total_elapsed_time DESC;

The 60-second threshold is only an example. Compare runtime with the normal baseline for the workload before treating a request as abnormal.

9. Deadlock Monitoring

For deadlock troubleshooting, Extended Events provides much more useful information than simply querying the current lock DMV.

The built-in system_health session commonly captures xml_deadlock_report events.

SELECT
    xed.value('@timestamp', 'datetime2') AS DeadlockTime,
    xed.query('.') AS DeadlockEvent
FROM
(
    SELECT CAST(target_data AS xml) AS TargetData
    FROM sys.dm_xe_session_targets AS st
    INNER JOIN sys.dm_xe_sessions AS s
        ON s.address = st.event_session_address
    WHERE s.name = 'system_health'
      AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes
(
    'RingBufferTarget/event[@name="xml_deadlock_report"]'
) AS XEventData(xed)
ORDER BY DeadlockTime DESC;

The deadlock graph helps identify participating sessions, objects, lock modes, statements, and the selected deadlock victim.

10. Wait Statistics

SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS WaitSeconds,
    signal_wait_time_ms / 1000.0 AS SignalWaitSeconds,
    (wait_time_ms - signal_wait_time_ms) / 1000.0
        AS ResourceWaitSeconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;

Wait statistics are cumulative. Compare waits across a meaningful interval rather than troubleshooting only from lifetime totals.

11. Failed SQL Server Agent Jobs

This version returns failed job outcome records rather than every failed step.

SELECT
    j.name AS JobName,
    msdb.dbo.agent_datetime
    (
        h.run_date,
        h.run_time
    ) AS RunDateTime,
    h.run_duration,
    h.message
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobhistory AS h
    ON j.job_id = h.job_id
WHERE h.step_id = 0
  AND h.run_status = 0
ORDER BY RunDateTime DESC;

12. Backup Health

For daily operations, it is more useful to check the most recent full, differential, and transaction log backups separately.

SELECT
    d.name AS DatabaseName,

    MAX(CASE
        WHEN b.type = 'D'
        THEN b.backup_finish_date
    END) AS LastFullBackup,

    MAX(CASE
        WHEN b.type = 'I'
        THEN b.backup_finish_date
    END) AS LastDifferentialBackup,

    MAX(CASE
        WHEN b.type = 'L'
        THEN b.backup_finish_date
    END) AS LastLogBackup

FROM sys.databases AS d

LEFT JOIN msdb.dbo.backupset AS b
    ON d.name = b.database_name

WHERE d.database_id > 4

GROUP BY d.name

ORDER BY d.name;

Important: Backup history confirms that SQL Server recorded a backup operation. It does not replace regular restore testing.

13. Active User Connections

SELECT
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) AS UserSessions
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
  AND database_id IS NOT NULL
GROUP BY database_id
ORDER BY UserSessions DESC;

This represents the current database context for each user session and should not be interpreted as a perfect count of every database a session can access.

14. TempDB Usage

SELECT
    CAST(
        SUM(user_object_reserved_page_count)
        * 8.0 / 1024
        AS decimal(18,2)
    ) AS UserObjectsMB,

    CAST(
        SUM(internal_object_reserved_page_count)
        * 8.0 / 1024
        AS decimal(18,2)
    ) AS InternalObjectsMB,

    CAST(
        SUM(version_store_reserved_page_count)
        * 8.0 / 1024
        AS decimal(18,2)
    ) AS VersionStoreMB,

    CAST(
        SUM(unallocated_extent_page_count)
        * 8.0 / 1024
        AS decimal(18,2)
    ) AS FreeSpaceMB

FROM tempdb.sys.dm_db_file_space_usage;

If TempDB usage is unexpectedly high, investigate the workload consuming the space before shrinking TempDB files.

15. Always On Availability Group Health

For SQL Server instances participating in Availability Groups, review synchronization state, synchronization health, send queues, redo queues, and connected state.

SELECT
    ag.name AS AvailabilityGroup,
    ar.replica_server_name,
    DB_NAME(drs.database_id) AS DatabaseName,
    drs.is_local,
    drs.is_primary_replica,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.log_send_queue_size,
    drs.redo_queue_size,
    ars.connected_state_desc
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_replicas AS ar
    ON drs.replica_id = ar.replica_id
INNER JOIN sys.availability_groups AS ag
    ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars
    ON ar.replica_id = ars.replica_id
   AND ar.group_id = ars.group_id
ORDER BY
    ag.name,
    ar.replica_server_name,
    DatabaseName;

Interpret synchronization state according to the configured availability mode. For example, asynchronous replicas are not expected to behave exactly like synchronous-commit replicas.

16. One-Click Morning Health Check

The following read-only script combines several of the most useful checks into one quick DBA morning review.

/* =========================================
   SQL SERVER MORNING HEALTH CHECK
   Technical Architects Zone (TAZ)
   ========================================= */

-- 1. Instance Information
SELECT
    @@SERVERNAME AS ServerName,
    GETDATE() AS CurrentServerTime,
    sqlserver_start_time AS SQLServerStartTime,
    DATEDIFF(DAY, sqlserver_start_time, GETDATE())
        AS UptimeDays
FROM sys.dm_os_sys_info;


-- 2. Database State
SELECT
    name AS DatabaseName,
    state_desc,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
ORDER BY name;


-- 3. Operating-System Memory
SELECT
    total_physical_memory_kb / 1024
        AS TotalMemoryMB,
    available_physical_memory_kb / 1024
        AS AvailableMemoryMB,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;


-- 4. Disk Free Space
EXEC master.dbo.xp_fixeddrives;


-- 5. Transaction Log Space
DBCC SQLPERF(LOGSPACE);


-- 6. Current Blocking
SELECT
    r.session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.wait_type,
    r.wait_time AS WaitTime_ms,
    r.wait_resource,
    st.text AS QueryText
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;


-- 7. Requests Running Longer Than 60 Seconds
SELECT
    r.session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.status,
    r.command,
    r.total_elapsed_time / 1000.0
        AS DurationSeconds,
    r.cpu_time / 1000.0
        AS CPUSeconds,
    r.wait_type,
    r.blocking_session_id,
    st.text AS QueryText
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id <> @@SPID
  AND r.total_elapsed_time > 60000
ORDER BY r.total_elapsed_time DESC;


-- 8. Top Wait Statistics
SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0
        AS WaitSeconds,
    signal_wait_time_ms / 1000.0
        AS SignalWaitSeconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;


-- 9. Failed SQL Agent Jobs
SELECT
    j.name AS JobName,
    msdb.dbo.agent_datetime
    (
        h.run_date,
        h.run_time
    ) AS RunDateTime,
    h.message
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobhistory AS h
    ON j.job_id = h.job_id
WHERE h.step_id = 0
  AND h.run_status = 0
ORDER BY RunDateTime DESC;


-- 10. Last Full / Differential / Log Backup
SELECT
    d.name AS DatabaseName,

    MAX(CASE
        WHEN b.type = 'D'
        THEN b.backup_finish_date
    END) AS LastFullBackup,

    MAX(CASE
        WHEN b.type = 'I'
        THEN b.backup_finish_date
    END) AS LastDifferentialBackup,

    MAX(CASE
        WHEN b.type = 'L'
        THEN b.backup_finish_date
    END) AS LastLogBackup

FROM sys.databases AS d

LEFT JOIN msdb.dbo.backupset AS b
    ON d.name = b.database_name

WHERE d.database_id > 4

GROUP BY d.name

ORDER BY d.name;


-- 11. TempDB Usage
SELECT
    CAST(
        SUM(user_object_reserved_page_count)
        * 8.0 / 1024 AS decimal(18,2)
    ) AS UserObjectsMB,

    CAST(
        SUM(internal_object_reserved_page_count)
        * 8.0 / 1024 AS decimal(18,2)
    ) AS InternalObjectsMB,

    CAST(
        SUM(version_store_reserved_page_count)
        * 8.0 / 1024 AS decimal(18,2)
    ) AS VersionStoreMB,

    CAST(
        SUM(unallocated_extent_page_count)
        * 8.0 / 1024 AS decimal(18,2)
    ) AS FreeSpaceMB

FROM tempdb.sys.dm_db_file_space_usage;

Practical approach: Use this consolidated script for the initial morning review, then use the individual scripts above to drill deeper into any abnormal result.

Production DBA Health Checklist

• Instance: check unexpected SQL Server restarts.
• Database State: identify OFFLINE, SUSPECT, RECOVERY_PENDING, RESTORING, or other unexpected states.
• CPU: investigate sustained CPU pressure and expensive workloads.
• Memory: check available physical memory, SQL Server memory configuration, grants, and paging.
• Disk: check free capacity, growth trends, autogrowth, latency, and throughput.
• Transaction Log: check log utilization, growth, backups, and log reuse waits.
• Blocking: identify prolonged blocking chains and long-running transactions.
• Deadlocks: review deadlock graphs and recurring patterns.
• Long Queries: compare abnormal runtimes against the workload baseline.
• Waits: compare significant wait patterns over meaningful intervals.
• SQL Agent: review failed, disabled, or unexpectedly long-running jobs.
• Backups: verify full, differential, and transaction log backup health.
• TempDB: monitor user objects, internal objects, version store, free space, and growth.
• Always On: verify replica connectivity, synchronization health, send queues, and redo queues.
• Error Logs: review SQL Server, SQL Agent, cluster, and operating-system errors where relevant.

Daily Health Check Flow

SQL Server Instance
        |
        v
Database State
        |
        v
CPU & Memory
        |
        v
Disk & Database Files
        |
        v
Transaction Logs
        |
        v
Blocking & Long Queries
        |
        v
Wait Statistics
        |
        v
SQL Agent Jobs
        |
        v
Backup Health
        |
        v
TempDB
        |
        v
Always On Health
        |
        v
Review Exceptions

Key Takeaways

• A daily health check should start broad and drill down only where an abnormality appears.
• SQL Server uptime provides important context for cumulative DMV statistics.
• CPU troubleshooting should focus on workload behavior, not CPU percentage alone.
• Memory health should include operating-system and SQL Server memory behavior.
• Disk monitoring should consider available capacity together with expected database growth.
• Transaction log monitoring should include both utilization and log_reuse_wait_desc.
• Investigate a blocking chain before deciding whether a session should be terminated.
• Extended Events are more appropriate for deadlock investigation than simply listing active locks.
• Backup success should be complemented by regular test restores.
• Always On health requires more than a simple SYNCHRONIZED status check.
• Historical baselines make daily health-check results substantially more useful.

Conclusion

A SQL Server daily health check should provide an early indication of problems before they develop into production incidents.

The goal is not to look for one magic DMV or one fixed threshold. A DBA should combine instance health, workload behavior, CPU, memory, disk, transaction logs, blocking, wait statistics, SQL Agent jobs, backups, TempDB, and high-availability health to understand the overall state of the platform.

The approach I prefer is: check the platform → identify exceptions → compare with the normal baseline → investigate the affected area → find the root cause → validate the corrective action.

Over time, these checks can also be automated through SQL Server Agent, PowerShell, monitoring platforms, or centralized observability solutions rather than relying entirely on manual execution.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning