SQL Serverhealth check checklist

SELECT
    d.name AS database_name,
    d.state_desc,
    d.recovery_model_desc,
    d.compatibility_level,
    d.is_read_only,
    d.is_query_store_on,
    SUSER_SNAME(d.owner_sid) AS database_owner,
    CONVERT(decimal(18,2), SUM(mf.size) * 8.0 / 1024.0) AS size_mb,
    COUNT(mf.file_id) AS file_count
FROM sys.databases AS d
LEFT JOIN sys.master_files AS mf
    ON mf.database_id = d.database_id
WHERE d.name <> N'tempdb'
GROUP BY
    d.name,
    d.state_desc,
    d.recovery_model_desc,
    d.compatibility_level,
    d.is_read_only,
    d.is_query_store_on,
    d.owner_sid
ORDER BY size_mb DESC, d.name;

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    d.state_desc,
    MAX(CASE WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN bs.backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN bs.backup_finish_date END) AS last_copy_only_full_backup,
    MAX(CASE WHEN bs.type = 'D' THEN DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) END) AS last_full_duration_seconds
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.database_name = d.name
   AND bs.type = 'D'
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc, d.state_desc
ORDER BY last_full_backup, d.name;

WITH LastBackups AS (
    SELECT
        bs.database_name,
        bs.type,
        MAX(bs.backup_finish_date) AS last_backup_finish_date
    FROM msdb.dbo.backupset AS bs
    WHERE bs.type IN ('D', 'I', 'L')
    GROUP BY bs.database_name, bs.type
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN lb.type = 'D' THEN lb.last_backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN lb.type = 'I' THEN lb.last_backup_finish_date END) AS last_differential_backup,
    MAX(CASE WHEN lb.type = 'L' THEN lb.last_backup_finish_date END) AS last_log_backup,
    CASE
        WHEN d.recovery_model_desc IN (N'FULL', N'BULK_LOGGED')
         AND MAX(CASE WHEN lb.type = 'L' THEN lb.last_backup_finish_date END) IS NULL
        THEN N'Missing log backup history'
        ELSE N'Check timing against RPO'
    END AS review_note
FROM sys.databases AS d
LEFT JOIN LastBackups AS lb
    ON lb.database_name = d.name
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

SELECT TOP (100)
    rh.destination_database_name,
    rh.restore_date,
    rh.restore_type,
    rh.recovery,
    rh.replace,
    bs.database_name AS source_database_name,
    bs.backup_finish_date,
    bmf.physical_device_name
FROM msdb.dbo.restorehistory AS rh
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.backup_set_id = rh.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
ORDER BY rh.restore_date DESC;

SELECT TOP (100)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_date,
    h.run_time,
    h.run_duration,
    h.run_status,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -30, GETDATE()), 112))
  AND (
      h.step_name LIKE N'%CHECKDB%'
      OR h.message LIKE N'%CHECKDB%'
      OR h.step_name LIKE N'%integrity%'
      OR h.message LIKE N'%integrity%'
  )
ORDER BY h.instance_id DESC;

SELECT TOP (200)
    bs.database_name,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE bs.type
    END AS backup_type,
    bs.backup_finish_date,
    CONVERT(decimal(18,2), bs.backup_size / 1024.0 / 1024.0) AS backup_size_mb,
    CONVERT(decimal(18,2), bs.compressed_backup_size / 1024.0 / 1024.0) AS compressed_size_mb,
    bs.has_backup_checksums,
    bs.is_copy_only,
    bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
LEFT JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
WHERE bs.backup_finish_date >= DATEADD(day, -14, SYSDATETIME())
ORDER BY bs.backup_finish_date DESC;

SELECT TOP (100)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_date,
    h.run_time,
    h.run_duration,
    h.run_status,
    h.sql_severity,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_status = 0
  AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -14, GETDATE()), 112))
ORDER BY h.instance_id DESC;

SELECT
    o.name AS operator_name,
    o.enabled,
    o.email_address,
    o.last_email_date,
    o.last_email_time
FROM msdb.dbo.sysoperators AS o
ORDER BY o.enabled DESC, o.name;

SELECT
    j.name AS job_name,
    j.enabled AS job_enabled,
    j.notify_level_email,
    o.name AS email_operator_name
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysoperators AS o
    ON o.id = j.notify_email_operator_id
WHERE j.notify_level_email <> 0
   OR j.notify_email_operator_id <> 0
ORDER BY j.name;

SELECT
    p.name AS mail_profile_name,
    p.description
FROM msdb.dbo.sysmail_profile AS p
ORDER BY p.name;

SELECT
    j.name AS job_name,
    j.enabled AS job_enabled,
    s.name AS schedule_name,
    s.enabled AS schedule_enabled,
    h.run_date,
    h.run_time,
    h.run_duration,
    h.run_status
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobschedules AS js
    ON js.job_id = j.job_id
LEFT JOIN msdb.dbo.sysschedules AS s
    ON s.schedule_id = js.schedule_id
LEFT JOIN msdb.dbo.sysjobhistory AS h
    ON h.job_id = j.job_id
   AND h.step_id = 0
   AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -14, GETDATE()), 112))
WHERE j.name LIKE N'%backup%'
   OR j.name LIKE N'%check%'
   OR j.name LIKE N'%integrity%'
   OR j.name LIKE N'%index%'
   OR j.name LIKE N'%stat%'
   OR j.name LIKE N'%cleanup%'
ORDER BY j.name, h.instance_id DESC;

SELECT
    DB_NAME(mf.database_id) AS database_name,
    mf.type_desc,
    mf.name AS logical_file_name,
    mf.physical_name,
    CONVERT(decimal(18,2), mf.size * 8.0 / 1024.0) AS size_mb,
    CASE
        WHEN mf.is_percent_growth = 1 THEN CONCAT(mf.growth, N'%')
        ELSE CONCAT(CONVERT(decimal(18,2), mf.growth * 8.0 / 1024.0), N' MB')
    END AS growth_setting,
    CASE
        WHEN mf.max_size = -1 THEN N'Unlimited'
        WHEN mf.max_size = 0 THEN N'No growth'
        ELSE CONCAT(CONVERT(decimal(18,2), mf.max_size * 8.0 / 1024.0), N' MB')
    END AS max_size_setting,
    mf.is_percent_growth
FROM sys.master_files AS mf
WHERE DB_NAME(mf.database_id) <> N'tempdb'
ORDER BY database_name, mf.type_desc, mf.file_id;

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    CONVERT(decimal(18,2), SUM(CASE WHEN mf.type_desc = N'LOG' THEN mf.size ELSE 0 END) * 8.0 / 1024.0) AS log_size_mb,
    COUNT(CASE WHEN mf.type_desc = N'LOG' THEN 1 END) AS log_file_count
FROM sys.databases AS d
LEFT JOIN sys.master_files AS mf
    ON mf.database_id = d.database_id
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc, d.log_reuse_wait_desc
ORDER BY log_size_mb DESC, d.name;

SELECT
    df.file_id,
    df.name AS logical_file_name,
    df.type_desc,
    df.physical_name,
    CONVERT(decimal(18,2), df.size * 8.0 / 1024.0) AS file_size_mb,
    CASE
        WHEN df.is_percent_growth = 1 THEN CONCAT(df.growth, N'%')
        ELSE CONCAT(CONVERT(decimal(18,2), df.growth * 8.0 / 1024.0), N' MB')
    END AS growth_setting,
    CONVERT(decimal(18,2), fsu.unallocated_extent_page_count * 8.0 / 1024.0) AS free_space_mb,
    CONVERT(decimal(18,2), fsu.user_object_reserved_page_count * 8.0 / 1024.0) AS user_object_mb,
    CONVERT(decimal(18,2), fsu.internal_object_reserved_page_count * 8.0 / 1024.0) AS internal_object_mb,
    CONVERT(decimal(18,2), fsu.version_store_reserved_page_count * 8.0 / 1024.0) AS version_store_mb
FROM tempdb.sys.database_files AS df
LEFT JOIN sys.dm_db_file_space_usage AS fsu
    ON fsu.file_id = df.file_id
ORDER BY df.file_id;

WITH RingBuffer AS (
    SELECT CAST(t.target_data AS xml) AS target_data
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
        ON t.event_session_address = s.address
    WHERE s.name = N'system_health'
      AND t.target_name = N'ring_buffer'
),
Events AS (
    SELECT
        DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), xed.value(N'@timestamp', N'datetime2')) AS event_time,
        xed.value(N'(data[@name="error_number"]/value)[1]', N'int') AS error_number,
        xed.value(N'(data[@name="severity"]/value)[1]', N'int') AS severity,
        xed.value(N'(data[@name="message"]/value)[1]', N'nvarchar(4000)') AS message
    FROM RingBuffer
    CROSS APPLY target_data.nodes(N'//RingBufferTarget/event[@name="error_reported"]') AS XEventData(xed)
)
SELECT TOP (100)
    event_time,
    error_number,
    severity,
    message
FROM Events
ORDER BY event_time DESC;

WITH RingBuffer AS (
    SELECT CAST(t.target_data AS xml) AS target_data
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
        ON t.event_session_address = s.address
    WHERE s.name = N'system_health'
      AND t.target_name = N'ring_buffer'
),
Events AS (
    SELECT
        DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), xed.value(N'@timestamp', N'datetime2')) AS event_time,
        xed.value(N'(data[@name="error_number"]/value)[1]', N'int') AS error_number,
        xed.value(N'(data[@name="severity"]/value)[1]', N'int') AS severity,
        xed.value(N'(data[@name="message"]/value)[1]', N'nvarchar(4000)') AS message
    FROM RingBuffer
    CROSS APPLY target_data.nodes(N'//RingBufferTarget/event[@name="error_reported"]') AS XEventData(xed)
)
SELECT TOP (100)
    event_time,
    error_number,
    severity,
    message
FROM Events
WHERE severity >= 17
   OR error_number IN (823, 824, 825)
ORDER BY event_time DESC;

SELECT
    DB_NAME(database_id) AS database_name,
    file_id,
    page_id,
    event_type,
    error_count,
    last_update_date
FROM msdb.dbo.suspect_pages
ORDER BY last_update_date DESC;

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.open_transaction_count,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE r.session_id <> @@SPID
  AND (r.blocking_session_id <> 0 OR r.wait_type IS NOT NULL)
ORDER BY r.wait_time DESC;

SELECT
    st.session_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_tran_active_transactions AS at
JOIN sys.dm_tran_session_transactions AS st
    ON st.transaction_id = at.transaction_id
WHERE st.session_id <> @@SPID
ORDER BY transaction_age_seconds DESC;

SELECT TOP (25)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CONVERT(decimal(18,2), wait_time_ms / 1000.0) AS wait_seconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
    N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
    N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'LAZYWRITER_SLEEP',
    N'LOGMGR_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
    N'SERVER_IDLE_CHECK', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
    N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'XE_DISPATCHER_WAIT',
    N'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

SELECT
    desired_state_desc,
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc
FROM sys.database_query_store_options;

WITH RuntimeStats AS (
    SELECT
        q.query_id,
        qt.query_sql_text,
        rs.last_execution_time,
        rs.avg_duration,
        AVG(rs.avg_duration) OVER (PARTITION BY q.query_id) AS overall_avg_duration
    FROM sys.query_store_query AS q
    JOIN sys.query_store_query_text AS qt
        ON qt.query_text_id = q.query_text_id
    JOIN sys.query_store_plan AS p
        ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
        ON rs.plan_id = p.plan_id
    WHERE rs.last_execution_time >= DATEADD(day, -7, SYSDATETIME())
)
SELECT TOP (25)
    query_id,
    last_execution_time,
    CONVERT(decimal(18,2), avg_duration / 1000.0) AS avg_duration_ms,
    CONVERT(decimal(18,2), overall_avg_duration / 1000.0) AS overall_avg_duration_ms,
    LEFT(query_sql_text, 600) AS query_text_sample
FROM RuntimeStats
WHERE avg_duration > overall_avg_duration * 2
ORDER BY avg_duration DESC;

SELECT
    name,
    value,
    value_in_use,
    is_dynamic,
    is_advanced,
    description
FROM sys.configurations
WHERE name IN (
    N'max server memory (MB)',
    N'min server memory (MB)',
    N'max degree of parallelism',
    N'cost threshold for parallelism',
    N'backup compression default',
    N'optimize for ad hoc workloads',
    N'remote admin connections',
    N'xp_cmdshell'
)
ORDER BY name;

SELECT
    DB_NAME(mf.database_id) AS database_name,
    mf.type_desc,
    mf.name AS logical_file_name,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    CONVERT(decimal(18,2), vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads, 0)) AS avg_read_ms,
    CONVERT(decimal(18,2), vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0)) AS avg_write_ms,
    CONVERT(decimal(18,2), vfs.io_stall * 1.0 / NULLIF(vfs.num_of_reads + vfs.num_of_writes, 0)) AS avg_io_ms
FROM sys.master_files AS mf
CROSS APPLY sys.dm_io_virtual_file_stats(mf.database_id, mf.file_id) AS vfs
WHERE DB_NAME(mf.database_id) <> N'tempdb'
ORDER BY avg_io_ms DESC, database_name, mf.file_id;

SELECT
    SERVERPROPERTY('ServerName') AS server_name,
    SERVERPROPERTY('MachineName') AS machine_name,
    SERVERPROPERTY('Edition') AS edition,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel') AS product_level,
    SERVERPROPERTY('ProductUpdateLevel') AS product_update_level,
    SERVERPROPERTY('IsClustered') AS is_clustered,
    SERVERPROPERTY('IsHadrEnabled') AS is_hadr_enabled,
    si.sqlserver_start_time,
    si.cpu_count,
    si.scheduler_count,
    si.physical_memory_kb / 1024 AS physical_memory_mb
FROM sys.dm_os_sys_info AS si;

SELECT
    roles.name AS server_role,
    members.name AS login_name,
    members.type_desc AS login_type,
    members.is_disabled
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS roles
    ON roles.principal_id = srm.role_principal_id
JOIN sys.server_principals AS members
    ON members.principal_id = srm.member_principal_id
ORDER BY roles.name, members.name;

SELECT
    servicename,
    startup_type_desc,
    status_desc,
    service_account,
    last_startup_time
FROM sys.dm_server_services
ORDER BY servicename;

SELECT
    c.name AS credential_name,
    c.credential_identity,
    p.name AS proxy_name,
    p.enabled AS proxy_enabled,
    s.subsystem
FROM sys.credentials AS c
LEFT JOIN msdb.dbo.sysproxies AS p
    ON p.credential_id = c.credential_id
LEFT JOIN msdb.dbo.sysproxysubsystem AS ps
    ON ps.proxy_id = p.proxy_id
LEFT JOIN msdb.dbo.syssubsystems AS s
    ON s.subsystem_id = ps.subsystem_id
ORDER BY c.name, p.name, s.subsystem;

SELECT
    name,
    value_in_use,
    description
FROM sys.configurations
WHERE name IN (
    N'xp_cmdshell',
    N'Ole Automation Procedures',
    N'clr enabled',
    N'external scripts enabled',
    N'Ad Hoc Distributed Queries'
)
ORDER BY name;

SELECT
    name AS linked_server_name,
    product,
    provider,
    data_source,
    is_linked,
    is_remote_login_enabled,
    is_rpc_out_enabled
FROM sys.servers
WHERE server_id <> 0
ORDER BY name;

SELECT
    a.name AS alert_name,
    a.enabled,
    a.message_id,
    a.severity,
    a.delay_between_responses,
    a.last_occurrence_date,
    a.last_occurrence_time
FROM msdb.dbo.sysalerts AS a
ORDER BY a.enabled DESC, a.name;

SELECT
    a.name AS alert_name,
    o.name AS operator_name,
    o.email_address,
    n.notification_method
FROM msdb.dbo.sysnotifications AS n
JOIN msdb.dbo.sysalerts AS a
    ON a.id = n.alert_id
JOIN msdb.dbo.sysoperators AS o
    ON o.id = n.operator_id
ORDER BY a.name, o.name;

SELECT
    ag.name AS availability_group_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    ar.availability_mode_desc,
    ar.failover_mode_desc
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
    ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars
    ON ars.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name;

SELECT
    DB_NAME(drs.database_id) AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.database_state_desc,
    drs.log_send_queue_size,
    drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states AS drs
ORDER BY database_name;