The AlwaysOn_health XEvent session is installed by SQL Server by default, and started by the Create Availability Group wizard when a new availability group is created.

AlwaysOn_health Extended Event Session

The xEvent session collects events triggered by events directly affecting AlwaysOn availability group like;

• AlwaysOn errors
• AlwaysOn state transitions
• AlwaysOn DDL executed to create, drop, or modify the availability group

The following symptoms should lead to review of the AlwaysOn_Health data:

• Availability group unexpectedly in the Resolving state
• Cannot access availability databases
• Availability group in an indeterminate state like corrupt
• Unexpected availability group fail-over

If you script out the AlwaysOn_health extended event, you will get the below T-SQL. The AlwaysOn_health session detects significant number of errors like a dramatic shift in synchronization, state transition, ability to fail over, and many more.

CREATE EVENT SESSION [AlwaysOn_health] ON SERVER 
ADD EVENT sqlserver.alwayson_ddl_executed,
ADD EVENT sqlserver.availability_group_lease_expired,
ADD EVENT sqlserver.availability_replica_automatic_failover_validation,
ADD EVENT sqlserver.availability_replica_manager_state_change,
ADD EVENT sqlserver.availability_replica_state,
ADD EVENT sqlserver.availability_replica_state_change,
ADD EVENT sqlserver.error_reported(
    WHERE ([error_number]=(9691) OR [error_number]=(35204) OR [error_number]=(9693) OR 
[error_number]=(26024) OR [error_number]=(28047) OR [error_number]=(26023) OR
[error_number]=(9692) OR [error_number]=(28034) OR [error_number]=(28036) OR 
[error_number]=(28048) OR [error_number]=(28080) OR [error_number]=(28091) OR 
[error_number]=(26022) OR [error_number]=(9642) OR [error_number]=(35201) OR 
[error_number]=(35202) OR [error_number]=(35206) OR [error_number]=(35207) OR 
[error_number]=(26069) OR [error_number]=(26070) OR [error_number]>(41047) AND 
[error_number]<(41056) OR [error_number]=(41142) OR [error_number]=(41144) OR 
[error_number]=(1480) OR [error_number]=(823) OR [error_number]=(824) OR 
[error_number]=(829) OR [error_number]=(35264) OR [error_number]=(35265) OR 
[error_number]=(41188) OR [error_number]=(41189))
 ),
ADD EVENT sqlserver.hadr_db_partner_set_sync_state,
ADD EVENT sqlserver.lock_redo_blocked
ADD TARGET package0.event_file(SET filename=N'AlwaysOn_health.xel',max_file_size=(5),max_rollover_files=(4))
WITH (
MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON
)
GO

Enjoy Learning!

The post AlwaysOn_health Extended Event Session appeared first on .