SQL Server health 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_countFROM sys.databases AS dLEFT JOIN sys.master_files AS mf    ON mf.database_id = d.database_idWHERE 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_sidORDER 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_secondsFROM sys.databases AS dLEFT 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_descORDER 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_noteFROM sys.databases AS dLEFT JOIN LastBackups AS lb    ON lb.database_name = d.nameWHERE d.name <> N'tempdb'GROUP BY d.name, d.recovery_model_descORDER 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_nameFROM msdb.dbo.restorehistory AS rhLEFT JOIN msdb.dbo.backupset AS bs    ON bs.backup_set_id = rh.backup_set_idLEFT JOIN msdb.dbo.backupmediafamily AS bmf    ON bmf.media_set_id = bs.media_set_idORDER 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.messageFROM msdb.dbo.sysjobhistory AS hJOIN msdb.dbo.sysjobs AS j    ON j.job_id = h.job_idWHERE 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_nameFROM msdb.dbo.backupset AS bsLEFT JOIN msdb.dbo.backupmediafamily AS bmf    ON bmf.media_set_id = bs.media_set_idWHERE 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.messageFROM msdb.dbo.sysjobhistory AS hJOIN msdb.dbo.sysjobs AS j    ON j.job_id = h.job_idWHERE 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_timeFROM msdb.dbo.sysoperators AS oORDER 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_nameFROM msdb.dbo.sysjobs AS jLEFT JOIN msdb.dbo.sysoperators AS o    ON o.id = j.notify_email_operator_idWHERE j.notify_level_email <> 0   OR j.notify_email_operator_id <> 0ORDER BY j.name; SELECT    p.name AS mail_profile_name,    p.descriptionFROM msdb.dbo.sysmail_profile AS pORDER 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_statusFROM msdb.dbo.sysjobs AS jLEFT JOIN msdb.dbo.sysjobschedules AS js    ON js.job_id = j.job_idLEFT JOIN msdb.dbo.sysschedules AS s    ON s.schedule_id = js.schedule_idLEFT 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_growthFROM sys.master_files AS mfWHERE 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_countFROM sys.databases AS dLEFT JOIN sys.master_files AS mf    ON mf.database_id = d.database_idWHERE d.name <> N'tempdb'GROUP BY d.name, d.recovery_model_desc, d.log_reuse_wait_descORDER 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_mbFROM tempdb.sys.database_files AS dfLEFT JOIN sys.dm_db_file_space_usage AS fsu    ON fsu.file_id = df.file_idORDER 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,    messageFROM EventsORDER 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,    messageFROM EventsWHERE 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_dateFROM msdb.dbo.suspect_pagesORDER 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_nameFROM sys.dm_exec_requests AS rJOIN sys.dm_exec_sessions AS s    ON s.session_id = r.session_idWHERE 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_stateFROM sys.dm_tran_active_transactions AS atJOIN sys.dm_tran_session_transactions AS st    ON st.transaction_id = at.transaction_idWHERE st.session_id <> @@SPIDORDER 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_secondsFROM sys.dm_os_wait_statsWHERE 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_descFROM 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_sampleFROM RuntimeStatsWHERE avg_duration > overall_avg_duration * 2ORDER BY avg_duration DESC;

SELECT    name,    value,    value_in_use,    is_dynamic,    is_advanced,    descriptionFROM sys.configurationsWHERE 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_msFROM sys.master_files AS mfCROSS APPLY sys.dm_io_virtual_file_stats(mf.database_id, mf.file_id) AS vfsWHERE 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_mbFROM 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_disabledFROM sys.server_role_members AS srmJOIN sys.server_principals AS roles    ON roles.principal_id = srm.role_principal_idJOIN sys.server_principals AS members    ON members.principal_id = srm.member_principal_idORDER BY roles.name, members.name;

SELECT    servicename,    startup_type_desc,    status_desc,    service_account,    last_startup_timeFROM sys.dm_server_servicesORDER BY servicename; SELECT    c.name AS credential_name,    c.credential_identity,    p.name AS proxy_name,    p.enabled AS proxy_enabled,    s.subsystemFROM sys.credentials AS cLEFT JOIN msdb.dbo.sysproxies AS p    ON p.credential_id = c.credential_idLEFT JOIN msdb.dbo.sysproxysubsystem AS ps    ON ps.proxy_id = p.proxy_idLEFT JOIN msdb.dbo.syssubsystems AS s    ON s.subsystem_id = ps.subsystem_idORDER BY c.name, p.name, s.subsystem;

SELECT    name,    value_in_use,    descriptionFROM sys.configurationsWHERE 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_enabledFROM sys.serversWHERE server_id <> 0ORDER 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_timeFROM msdb.dbo.sysalerts AS aORDER BY a.enabled DESC, a.name; SELECT    a.name AS alert_name,    o.name AS operator_name,    o.email_address,    n.notification_methodFROM msdb.dbo.sysnotifications AS nJOIN msdb.dbo.sysalerts AS a    ON a.id = n.alert_idJOIN msdb.dbo.sysoperators AS o    ON o.id = n.operator_idORDER 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_descFROM sys.availability_groups AS agJOIN sys.availability_replicas AS ar    ON ar.group_id = ag.group_idLEFT JOIN sys.dm_hadr_availability_replica_states AS ars    ON ars.replica_id = ar.replica_idORDER 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_sizeFROM sys.dm_hadr_database_replica_states AS drsORDER BY database_name;