Introduction 🌐

Always On Availability Groups (AG) in SQL Server provide high availability and disaster recovery solutions for databases. Ensuring the health of your AG is crucial to maintaining a resilient database environment. In this blog post, we’ll guide you through a T-SQL script that performs a comprehensive health check for a single database within an AG. This script will be executed on the primary replica and includes valuable information such as synchronization state, database state, and secondary lag in seconds.

Requirements 🛠️

Before diving into the health check script, make sure you meet the following prerequisites:

1. SQL Server Availability Group: Ensure that your SQL Server instance is configured with an Availability Group containing the target database.
2. Permission: The account executing the script should have the necessary permissions to query the sys.dm_hadr_database_replica_states dynamic management view.
3. Primary Replica: Run the script on the primary replica of the Availability Group.

T-SQL Script 📜

Here’s the T-SQL script that will provide a detailed health check for a specific database within your Availability Group:

select 

    db_name(database_id) as [Database],

    is_primary_replica,

    synchronization_state_desc,

    database_state_desc,

    is_suspended,

    suspend_reason_desc,

    recovery_lsn,

    truncation_lsn,

    last_sent_lsn,

    last_sent_time,

    last_received_lsn,

    last_received_time,

    last_hardened_lsn,

    log_send_queue_size,

    log_send_rate,

    redo_queue_size,

    redo_rate,

    end_of_log_lsn,

    last_commit_lsn,

    last_commit_time,

    secondary_lag_seconds 

from 

    sys.dm_hadr_database_replica_states

This script retrieves essential information about the specified database, such as synchronization state, log queue details, and secondary replica lag in seconds.

Conclusion 🎉

Regularly monitoring the health of your Always On Availability Group is fundamental to ensuring the stability and reliability of your SQL Server databases. By utilizing the provided T-SQL script, you can quickly assess the state of a single database within the AG, identify any potential issues, and take proactive measures to maintain a robust database infrastructure.

Remember to schedule periodic health checks to catch any anomalies early, minimizing the risk of downtime and data loss in your SQL Server environment. Stay vigilant, and may your databases always be available and resilient! 💪

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.