SQL Server 2017 AlwaysOn AG 自动初始化（八）

监控自动种子设定

• 系统动态管理视图

sys.dm_hadr_automatic_seeding

在主要副本上，查询 sys.dm_hadr_automatic_seeding 以检查自动种子设定过程的状态。 对于每个种子设定过程，该视图都将返回一行。 例如：

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

在主要副本上，查询 sys.dm_hadr_physical_seeding_stats DMV 以查看当前运行的每个种子设定过程的物理统计信息。 种子设定正在运行时，以下查询将返回多行：

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

• 错误日志

• 扩展事件

下表列出了与自动种子设定相关的扩展事件：

创建扩展事件

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

分析扩展事件

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].[EventXML].[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].[EventXML].[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].[EventXML].[value]('(/event/data[@name="required_seeding"]/value)[1]', 'VARCHAR(8000)') AS [required_seeding],/*hadr_automatic_seeding_timeout*/[ds].[EventXML].[value]('(/event/data[@name="timeout_ms"]/value)[1]', 'BIGINT') as [timeout_ms],/*hadr_automatic_seeding_failure*/[ds].[EventXML].[value]('(/event/data[@name="failure_state"]/value)[1]', 'BIGINT') as [failure_state],[ds].[EventXML].[value]('(/event/data[@name="failure_state_desc"]/value)[1]', 'VARCHAR(8000)') AS [failure_state_desc]FROM [#DirectSeed] AS [ds]ORDER BY [ds].[EventXML].[value]('(/event/@timestamp)[1]', 'DATETIME2(7)') DESC