监控自动种子设定
系统动态管理视图
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_code
FROM 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_enabled
FROM sys.dm_hadr_physical_seeding_stats
错误日志
扩展事件
下表列出了与自动种子设定相关的扩展事件：
属性 | 描述 |
hadr_db_manager_seeding_request_msg | 种子设定请求消息 |
hadr_physical_seeding_backup_state_change | 物理种子设定备份端状态更改 |
hadr_physical_seeding_restore_state_change | 物理种子设定还原端状态更改 |
hadr_physical_seeding_forwarder_state_change | 物理种子设定转发器端状态更改 |
hadr_physical_seeding_forwarder_target_state_change | 物理种子设定转发器目标端状态更改 |
hadr_physical_seeding_submit_callback | 物理种子设定提交回调事件 |
hadr_physical_seeding_failure | 物理种子设定失败事件 |
hadr_physical_seeding_progress | 物理种子设定进度事件 |
hadr_physical_seeding_schedule_long_task_failure | 物理种子设定计划长任务失败事件 |
hadr_automatic_seeding_start | 在提交自动种子设定操作时发生 |
hadr_automatic_seeding_state_transition | 在自动种子设定操作更改状态时发生 |
hadr_automatic_seeding_success | 在自动种子设定操作成功时发生 |
hadr_automatic_seeding_failure | 在自动种子设定操作失败时发生 |
hadr_automatic_seeding_timeout | 在自动种子设定操作超时时发生 |
创建扩展事件
CREATE EVENT SESSION [DirectSeed] ON SERVER
ADD 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 SERVER
ADD 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 = START
ALTER 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],
[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],
[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],
[ds].[EventXML].[value]('(/event/data[@name="required_seeding"]/value)[1]', 'VARCHAR(8000)') AS [required_seeding],
[ds].[EventXML].[value]('(/event/data[@name="timeout_ms"]/value)[1]', 'BIGINT') as [timeout_ms],
[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
