[AlwaysOn availability Groups] AG Expansion Event

AGExtended Events

SQL Server defines some of the AlwaysOn Extended Events. You can monitor these extended events to help diagnose The underlying problem with AG. You can also use the following statements to view extended events:

SELECT * from sys.dm_xe_objects WHERE name like '%hadr% '

1.AlwaysOnHealth(alwayson_health)Session

Alwayson_health extended session When you are creating AG and Capture AlwaysOn a subset of related events. This session is configured as a useful and handy tool to help you turn on debug AG. The Create AG window automatically starts the session in each specific availability replica configuration window.

If you are not using the new AG dialog box, Alwayson_health The session may not start automatically, and if the session does not start, you cannot capture each data when the problem occurs. You should start the session manually and configure the session to automatically session.

by viewing alwayson_ Health session definition:
1.SSMS Span style= "font-family: ' Microsoft Jacob Black ', Sans-serif;" >, expand extended Events, session
2. right-click alwayson_health, Then create the script

more about Alwayson_health information to view: alwaysonextended Events Reference .

2.Debugging extended Transactions

If you want to have additional event overridesAlwayson_healthSession,SQL Serverdefines a large set of debug events. Steps need to be made:
1.in theSSMS, expand Extended Events, Session
2.Right-click Create time, or right-clickAlwayson_health, select Properties
3.Select Event Page
4.in theLibrary, catalog column, selectAlwaysOnand Clear all directories
5.in theChannelcolumn, select Debug, all related events, not selected will be displayed in theEvent Library
6.event LibrarySelect a, and then tap">"Button
7.when the session is complete, clickOKclosed. Ensure that the session has been started.

3.AlwaysOnExtended Event Reference

AlwaysOn extended events are used to track AG

3.1 Availability_replica_state_change

when AG change in status. Create or add an available copy of the AG. Can be used to diagnose automatic failover of errors. Can be used to track the transfer steps.

3.2 availability_gruop_lease_expired

when the cluster and available groups have connectivity issues and the lease expires. This event indicates that the AG and WSFC cluster connections are disconnected. If the connection problem occurs in the primary replica. Events may automatically transfer or cause AG offline.

3.3 availability_replica_automatic_failover_validation

when automatic failover verification AG as a Primary copy is necessary to show whether the target available copy is ready to become a new Primary the preparation of the copy. For example, failover validation returns an error when not all databases are synchronized, or if they are not already joined. This event is designed to provide the wrong point when a failover occurs. This information can be used to check why automatic failover occurs.

3.4 error_reported:for transport or link issues

Each filtering event indicates that a connection problem occurred, database mirroring Endpoint error occurred

3.5 Data_movement_suspend_resume

when a database copy Movement triggers the event when it hangs or resumes.

3.6 alwayson_ddl_executed

when it happens Alwason DDL statements, including Create , Alter,drop statement. The primary purpose of the event is to describe the behavior of the user in AG, or to indicate the starting point for user action behavior. Events can track manual failovers, force failover, interrupt, and recover data movement.

3.7 Alwayson_replica_manager_stat

triggered when the availability replica management state is modified. This event illustrates the heartbeat that the availability replica manages. When availability replica management does not have a heartbeat state, All availability replicas in the SQL Server instance are offline.

3.8 error_reported, Database copy role modification

of this filter, AG when a character changes, error_reported The event is triggered asynchronously. This indicates which availability database failed to modify the role while modifying the failover.

[AlwaysOn availability Groups] AG Expansion Event