Automatic initialization of SQL Server 2017 AlwaysOn AG (8)

Monitor automatic seed settings

System dynamic management view

Sys.dm_hadr_automatic_seeding

On the master copy, query sys.dm_hadr_automatic_seeding to check the status of the automatic seed setting process. For each seed setup process, the view returns a row. For example:

SELECT start_time, completion_time, is_source, current_state, failure_state, failure_state_desc, error_codeFROM sys.dm_hadr_automatic_seeding

Sys.dm_hadr_physical_seeding_stats

On the master copy, query sys.dm_hadr_physical_seeding_stats DMV to see the physical statistics for each seed setup process that is currently running. While the seed setting is running, the following query returns multiple rows:

SELECT local_database_name, role_desc, internal_state_desc, transfer_rate_bytes_per_second, transferred_size_bytes, database_size_bytes, start_time_utc, end_time_utc, estimate_time_complete_utc, total_disk_io_wait_time_ms, total_network_wait_time_ms, is_compression_enabledFROM sys.dm_hadr_physical_seeding_stats error log

Extended event

The following table lists the extension events related to automatic seed settings:

Attribute

Description

Hadr_db_manager_seeding_request_msg

Seed setting request message

Hadr_physical_seeding_backup_state_change

Physical seed setting backup status change

Hadr_physical_seeding_restore_state_change

Physical seed setting restore end status change

Hadr_physical_seeding_forwarder_state_change

Physical seed setting transponder state change

Hadr_physical_seeding_forwarder_target_state_change

Physical seed setting transponder destination state change

Hadr_physical_seeding_submit_callback

Physical seed settings submit callback event

Hadr_physical_seeding_failure

Physical seed setting failure event

Hadr_physical_seeding_progress

Physical seed setting progress event

Hadr_physical_seeding_schedule_long_task_failure

Physical seed setting schedule long task failure event

Hadr_automatic_seeding_start

Occurs when an automatic seed setting operation is submitted

Hadr_automatic_seeding_state_transition

Occurs when the automatic seed setting operation changes the state

Hadr_automatic_seeding_success

Occurs when the automatic seed setting operation is successful

Hadr_automatic_seeding_failure

Occurs when an automatic seed setting operation fails

Hadr_automatic_seeding_timeout

Occurs when the automatic seed setting operation times out

Create an extension event

CREATE EVENT SESSION [DirectSeed] ON SERVERADD EVENT sqlserver.hadr_ar_controller_debug (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_automatic_seeding_failure (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_automatic_seeding_start (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)) ADD EVENT sqlserver.hadr_automatic_seeding_state_transition (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_automatic_seeding_success (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_automatic_seeding_timeout (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)) ADD TARGET package0.event_file (SET filename=N'C:\ XE\ DirectSeed.xel' Max_rollover_files= (10) GO CREATE EVENT SESSION [PhysicalSeed] ON SERVERADD EVENT sqlserver.hadr_physical_seeding_backup_state_change (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_physical_seeding_failure (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_physical_seeding_forwarder_state_change (ACTION (sqlserver.database_id,sqlserver.sql_text)) Sqlserver.tsql_stack), ADD EVENT sqlserver.hadr_physical_seeding_forwarder_target_state_change (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_physical_seeding_progress (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_physical_seeding_restore_state_change (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)) ADD EVENT sqlserver.hadr_physical_seeding_schedule_long_task_failure (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.hadr_physical_seeding_submit_callback (ACTION (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)) ADD TARGET package0.event_file (SET filename=N'C:\ XE\ PhysicalSeed',max_rollover_files= (10)) GO ALTER EVENT SESSION [DirectSeed] ON SERVER STATE = STARTALTER EVENT SESSION [PhysicalSeed] ON SERVER STATE = START

Analyze extended events

IF OBJECT_ID ('tempdb..#DirectSeed') IS NOT NULL DROP TABLE [# DirectSeed]; CREATE TABLE [# DirectSeed] ([ID] INT IDENTITY (1,1) NOT NULL, [EventXML] XML, CONSTRAINT [PK_DirectSeed] PRIMARY KEY CLUSTERED ([ID])) INSERT [# DirectSeed] ([EventXML]) SELECT CONVERT (XML, [event_data]) AS [EventXML] FROM [sys]. [fn_xe_file_target_read_file] ('C:\ XE\ DirectSeed*.xel', NULL, NULL, NULL) CREATE PRIMARY XML INDEX [DirectSeedXML] ON [# DirectSeed] ([EventXML]); CREATE XML INDEX [DirectSeedXMLPath] ON [# DirectSeed] ([EventXML]) USING XML INDEX [DirectSeedXML] FOR VALUE Select [DS] .[ EventXML] .[ value] ('(/ event/@name) [1]', 'VARCHAR (MAX)') AS [event_name], [ds]. [EventXML]. [value] ('(/ event/@timestamp) [1]', 'DATETIME2 (7)') AS [event_time], [ds]. [value] ('(/ event/data [@ name= "debug_message"] / value) [1]' 'VARCHAR (8000)') AS [debug_message], / * hadr_automatic_seeding_state_transition*/ [DS]. [EventXML]. [value] ('(/ event/data [@ name= "previous_state"] / value) [1]', 'VARCHAR (8000)') AS [previous_state], [ds]. [EventXML]. [value] ('(/ event/data [@ name= "current_state"] / value) [1]', 'VARCHAR (8000)') AS [current_state] / * hadr_automatic_seeding_start*/ [DS] .[ EventXML] .[ value] ('(/ event/data [@ name= "operation_attempt_number"] / value) [1]', 'BIGINT') as [operation_attempt_number], [ds]. [value] (' (/ event/data [@ name= "ag_id"] / value) [1]', 'VARCHAR (8000)') AS [ag_id] [ds]. [EventXML]. [value] ('(/ event/data [@ name= "ag_db_id"] / value) [1]', 'VARCHAR (8000)') AS [ag_id], [ds]. [EventXML]. [value] ('(/ event/data [@ name= "ag_remote_replica_id"] / value) [1]', 'VARCHAR (8000)') AS [ag_remote_replica_id] / * hadr_automatic_seeding_success*/ [DS] .[ value] ('(/ event/data [@ name= "required_seeding"] / value) [1]', 'VARCHAR (8000)') AS [value], / * hadr_automatic_seeding_timeout*/ [DS] .[ EventXML] .[ value] ('(/ event/data [@ name= "timeout_ms"] / value) [1]', 'BIGINT') as [value] / * hadr_automatic_seeding_failure*/ [DS] .[ EventXML] .[ value] ('(/ event/data [@ name= "failure_state"] / value) [1]', 'BIGINT') as [failure_state], [ds]. [value] (' (/ event/data [@ name= "failure_state_desc"] / value) [1]' 'VARCHAR (8000)') AS [failure_state_desc] FROM [# DirectSeed] AS [ds] ORDER BY [ds]. [value] ('(/ event/@timestamp) [1]', 'DATETIME2 (7)') DESC