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

冰恋

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