Health of availability groups, database mirroring and log shipping, whichever this server uses. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.

-- 12_High_Availability.sql - availability groups, mirroring and log shipping health (whichever is used). Read-only.
SET NOCOUNT ON;
IF SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN
    SELECT ag.name AS ag, ar.replica_server_name, rs.role_desc, ar.availability_mode_desc, ar.failover_mode_desc,
           rs.connected_state_desc, rs.synchronization_health_desc, rs.last_connect_error_description
    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 rs ON rs.replica_id = ar.replica_id;

    SELECT ag.name AS ag, ar.replica_server_name, DB_NAME(drs.database_id) AS [database], drs.synchronization_state_desc,
           drs.synchronization_health_desc, drs.log_send_queue_size AS send_queue_kb, drs.redo_queue_size AS redo_queue_kb,
           drs.last_commit_time, drs.is_suspended, drs.suspend_reason_desc
    FROM sys.dm_hadr_database_replica_states AS drs
    JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
    JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
    ORDER BY ag, [database], ar.replica_server_name;
END
ELSE PRINT N'Availability groups are not enabled on this server.';

SELECT DB_NAME(database_id) AS mirrored_database, mirroring_role_desc, mirroring_state_desc, mirroring_safety_level_desc, mirroring_partner_instance
FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL;

SELECT primary_server, primary_database, last_backup_date, last_backup_file FROM msdb.dbo.log_shipping_monitor_primary;
SELECT secondary_server, secondary_database, last_copied_date, last_restored_date, last_restored_latency
FROM msdb.dbo.log_shipping_monitor_secondary;