SQL Server Always On Availability Groups are a robust solution for achieving high availability and disaster recovery for SQL Server databases. However, simply configuring them is not enough\u2014you also need a solid monitoring strategy to ensure data integrity and system reliability. One key aspect of this monitoring process is keeping an eye on lease timeouts<\/strong>, which can signal larger issues and help prevent potentially catastrophic split-brain scenarios.<\/p>\n In this post, we\u2019ll walk through the various health checks available for Always On Availability Groups, discuss how lease timeouts work, and explore practical methods for monitoring and troubleshooting.<\/p>\n Querying Lease Timeout Events:<\/strong><\/p>\n This query filters for the lease timeout error ( (Note: The value is in milliseconds, e.g., 30000 ms = 30 seconds.)<\/em><\/li>\n<\/ol>\n Monitoring SQL Server Always On Availability Groups is critical for safeguarding high availability and data integrity. From health checks to lease timeouts, each aspect requires careful configuration and ongoing attention. By using the built-in tools (SSMS, Extended Events, SQL Server Agent alerts), third-party or custom scripts, and best practices outlined here, you can proactively identify and address issues\u2014ensuring your Always On environment remains stable, performant, and ready to handle unexpected outages.<\/p>\n","protected":false},"excerpt":{"rendered":" SQL Server Always On Availability Groups are a robust solution for achieving high availability and disaster recovery for SQL Server databases. However, simply configuring them is not enough\u2014you also need a solid monitoring strategy to ensure data integrity and system reliability. One key aspect of this monitoring process is keeping an eye on lease timeouts, which can signal larger issues and help prevent potentially catastrophic split-brain scenarios. In this post, we\u2019ll walk through the various health checks available for Always On Availability Groups, discuss how lease timeouts work, and explore practical methods for monitoring and troubleshooting.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[36,116,219],"tags":[191,454,247,210,42,455,384,456,131,402],"class_list":["post-855","post","type-post","status-publish","format-standard","hentry","category-availability-groups","category-connectivity","category-windows-failover-cluster","tag-always-on-availability-groups","tag-database-monitoring","tag-disaster-recovery","tag-failover-cluster","tag-high-availability","tag-lease-timeout","tag-performance-monitoring","tag-replica-health","tag-sql-server","tag-sql-server-management-studio"],"yoast_head":"\nUnderstanding Health Checks in Always On Availability Groups<\/h2>\n
1. Database Health Checks<\/h3>\n
\n
2. Replica Health Checks<\/h3>\n
\n
3. Cluster Health Checks<\/h3>\n
\n
Lease Timeout in Always On<\/h2>\n
\n
Monitoring Health Checks and Lease Timeouts<\/h2>\n
1. Using SQL Server Management Studio (SSMS)<\/h3>\n
\n
2. Extended Events<\/h3>\n
\n
system_health<\/code> session to capture key events.<\/li>\n<\/ul>\nSELECT\n event_data.value('(event\/@name)[1]', 'varchar(50)') AS event_name,\n event_data.value('(event\/@timestamp)[1]', 'datetime') AS [timestamp],\n event_data.value('(event\/data[@name=\"message\"]\/value)[1]', 'varchar(max)') AS message\nFROM\n (SELECT CAST(target_data AS XML) AS event_data\n FROM sys.dm_xe_session_targets t\n JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address\n WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer') AS x\nWHERE\n event_data.value('(event\/@name)[1]', 'varchar(50)') = 'error_reported'\n AND event_data.value('(event\/data[@name=\"error_number\"]\/value)[1]', 'int') = 19407;<\/code><\/pre>\nerror_number = 19407<\/code>) reported in Extended Events.<\/p>\n3. SQL Server Agent Alerts<\/h3>\n
\n
19407<\/code> (lease timeout error).<\/li>\n4. Performance Monitor (PerfMon)<\/h3>\n
\n
5. Failover Cluster Manager<\/h3>\n
\n
6. PowerShell Cmdlets<\/h3>\n
\n
Get-ClusterLog -Destination \"C:\\ClusterLogs\"<\/code><\/pre>\n<\/li>\n7. Custom Monitoring Scripts<\/h3>\n
\n
SELECT\n ag.name AS AvailabilityGroupName,\n ar.replica_server_name,\n ar.availability_mode_desc,\n ar.failover_mode_desc,\n ar.synchronization_health_desc\nFROM sys.availability_replicas ar\nJOIN sys.availability_groups ag ON ar.group_id = ag.group_id;<\/code><\/pre>\n<\/li>\nSELECT\n db_name(database_id) AS DatabaseName,\n synchronization_state_desc,\n is_failover_ready\nFROM sys.dm_hadr_database_replica_states; <\/code><\/pre>\n<\/li>\n<\/ul>\nBest Practices for Monitoring<\/h2>\n
\n
Troubleshooting Lease Timeouts<\/h2>\n
Common Causes<\/h3>\n
\n
Steps to Resolve<\/h3>\n
\n
Import-Module FailoverClusters\nGet-ClusterResource -Name \"AG Resource Name\" | Set-ClusterParameter -Name LeaseTimeout -Value 30000 <\/code><\/pre>\nConclusion<\/h2>\n