–Below SQL Script can we use for check SQL Server AG HEALTH
USE [TempDB]
SET NOCOUNT ON
GO
SELECT CurrentTime_UTC = SYSUTCDATETIME()
DECLARE @XELTarget VARCHAR(MAX);
DECLARE @XELPath VARCHAR(MAX);
DECLARE @XELFile VARCHAR(max);
IF EXISTS(SELECT name FROM sys.dm_xe_sessions WHERE name = ‘AlwaysOn_Health’) BEGIN
SELECT @XELTarget = cast(xet.target_data AS XML).value(‘(EventFileTarget/File/@name)[1]’, ‘VARCHAR(MAX)’)
FROM sys.dm_xe_sessions xes
INNER JOIN sys.dm_xe_session_targets xet
ON xes.address = xet.event_session_address
WHERE xet.target_name = ‘event_file’ and xes.name = ‘AlwaysOn_Health’
SELECT @XELPath = REVERSE(SUBSTRING(REVERSE(@XELTarget),
CHARINDEX(‘\’, reverse(@XELTarget)),
LEN(@XELTarget)+1- CHARINDEX(‘\’, REVERSE(@XELTarget))))
SELECT @XELFile = @XELPath + ‘AlwaysOn_health*.xel’
IF @XELFile IS NULL BEGIN
PRINT ‘Unable to find XEVent target files for AlwaysOn_Health XEvent session’
PRINT ‘Expected AOHealth XEvent files in this location:’
PRINT @XELPath
RETURN
END
END ELSE BEGIN
PRINT ‘No AlwaysOn Health XEvent session found’
RETURN
END
–create table
DECLARE @AOHealth_XELData TABLE –CREATE TABLE @AOHealth_XELData
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
object_name varchar(max),
EventData XML,
file_name varchar(max),
file_offset bigint);
–read from the files into the table
IF @XELFile IS NOT NULL BEGIN
INSERT INTO @AOHealth_XELData
SELECT object_name, cast(event_data as XML) AS EventData,
file_name, File_Offset
FROM sys.fn_xe_file_target_read_file(
@XELFile, NULL, null, null);
END
— Create table for “error_reported” events
DECLARE @error_reported TABLE –CREATE TABLE @error_reported
(Xevent varchar(15),
TimeStamp_UTC varchar(75), –because sometimes it’s a long integer because of an internal bug
error_number INT,
severity INT,
state INT,
user_defined varchar(5),
category_desc varchar(25),
category varchar(5),
destination varchar(20),
destination_desc varchar(20),
is_intercepted varchar(5),
message varchar(max))
INSERT INTO @error_reported
SELECT CAST(object_name as varchar(15)) AS Xevent,
CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”error_number”]/value)[1]’, ‘int’) AS error_number,
EventData.value(‘(event/data[@name=”severity”]/value)[1]’, ‘int’) AS severity,
EventData.value(‘(event/data[@name=”state”]/value)[1]’, ‘int’) AS state,
EventData.value(‘(event/data[@name=”user_defined”]/value)[1]’, ‘varchar(5)’) AS user_defined,
EventData.value(‘(event/data[@name=”category”]/text)[1]’, ‘varchar(25)’) AS category_desc,
EventData.value(‘(event/data[@name=”category”]/value)[1]’, ‘varchar(5)’) AS category,
EventData.value(‘(event/data[@name=”destination”]/value)[1]’, ‘varchar(20)’) AS destination,
EventData.value(‘(event/data[@name=”destination”]/text)[1]’, ‘varchar(20)’) AS destination_desc,
EventData.value(‘(event/data[@name=”is_intercepted”]/value)[1]’, ‘varchar(5)’) AS is_intercepted,
EventData.value(‘(event/data[@name=”message”]/value)[1]’, ‘varchar(max)’) AS message
FROM @AOHealth_XELData
WHERE EventData.value(‘(event/@name)[1]’, ‘varchar(max)’) = ‘error_reported’;
IF EXISTS(SELECT * FROM @error_reported) BEGIN
PRINT ‘Error event stats’
PRINT ‘=================’;
–display results from “error_reported” event data
WITH ErrorCTE (ErrorNum, ErrorCount, FirstDate_UTC, LastDate_UTC) AS (
SELECT error_number, Count(error_number), min(TimeStamp_UTC), max(TimeStamp_UTC) As ErrorCount
FROM @error_reported
GROUP BY error_number)
SELECT ErrorNum,
ErrorCount,–CAST(ErrorCount as CHAR(10)) ErrorCount,
CONVERT(datetimeoffset(2), FirstDate_UTC,121) as FirstDate_UTC,
CONVERT(datetimeoffset(2), LastDate_UTC, 121) as LastDate_UTC,
CAST(CASE ErrorNum
WHEN 35202 THEN ‘A connection for availability group … has been successfully established…’
WHEN 1480 THEN ‘The %S_MSG database “%.*ls” is changing roles … because the AG failed over …’
WHEN 35206 THEN ‘A connection timeout has occurred on a previously established connection …’
WHEN 35201 THEN ‘A connection timeout has occurred while attempting to establish a connection …’
WHEN 41050 THEN ‘Waiting for local WSFC service to start.’
WHEN 41051 THEN ‘Local WSFC service started.’
WHEN 41052 THEN ‘Waiting for local WSFC node to start.’
WHEN 41053 THEN ‘Local WSFC node started.’
WHEN 41054 THEN ‘Waiting for local WSFC node to come online.’
WHEN 41055 THEN ‘Local WSFC node is online.’
WHEN 41048 THEN ‘Local WSFC service has become unavailable.’
WHEN 41049 THEN ‘Local WSFC node is no longer online.’
ELSE m.text END AS VARCHAR(81)) [Abbreviated Message]
FROM
ErrorCTE ec LEFT JOIN sys.messages m on ec.ErrorNum = m.message_id
and m.language_id = 1033
order by LastDate_UTC DESC, ErrorCount DESC
END
IF EXISTS(SELECT object_name FROM @AOHealth_XELData WHERE object_name = ‘alwayson_ddl_executed’)
BEGIN
PRINT ‘Non-failover DDL Events’;
PRINT ‘=======================’;
WITH AODDL (object_name, TimeStamp_UTC, ddl_action, ddl_action_desc, ddl_phase, ddl_phase_desc,
availability_group_name, availability_group_id, [statement])
AS
(
SELECT object_name, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”ddl_action”]/value)[1]’, ‘int’) AS ddl_action,
EventData.value(‘(event/data[@name=”ddl_action”]/text)[1]’, ‘varchar(15)’) AS ddl_action_desc,
EventData.value(‘(event/data[@name=”ddl_phase”]/value)[1]’, ‘int’) AS ddl_phase,
EventData.value(‘(event/data[@name=”ddl_phase”]/text)[1]’, ‘varchar(10)’) AS ddl_phase_desc,
EventData.value(‘(event/data[@name=”availability_group_name”]/value)[1]’, ‘varchar(25)’) AS availability_group_name,
EventData.value(‘(event/data[@name=”availability_group_id”]/value)[1]’, ‘varchar(36)’) AS availability_group_id,
EventData.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS [statement]
FROM @AOHealth_XELData
WHERE object_name = ‘alwayson_ddl_executed’
AND EventData.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) NOT LIKE ‘%FAILOVER%’
OR (EventData.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) LIKE ‘%FAILOVER%’ AND
EventData.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) LIKE ‘%CREATE%’)
)
SELECT cast(object_name as varchar(22)) AS XEvent, TimeStamp_UTC, ddl_action, ddl_action_desc, ddl_phase,
ddl_phase_desc, availability_group_name, availability_group_id,
CASE WHEN LEN([statement]) > 220
THEN CAST([statement] as varchar(1155)) + char(10)
ELSE CAST(Replace([statement], char(10), ”) as varchar(220))
END as [statement]
FROM AODDL
ORDER BY TimeStamp_UTC desc;
PRINT ‘Failover DDL Events’;
PRINT ‘===================’;
— Display results “alwayson_ddl_executed” events
WITH AODDL (object_name, TimeStamp_UTC, ddl_action, ddl_action_desc, ddl_phase, ddl_phase_desc,
availability_group_name, availability_group_id, [statement])
AS
(
SELECT object_name, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”ddl_action”]/value)[1]’, ‘int’) AS ddl_action,
EventData.value(‘(event/data[@name=”ddl_action”]/text)[1]’, ‘varchar(15)’) AS ddl_action_desc,
EventData.value(‘(event/data[@name=”ddl_phase”]/value)[1]’, ‘int’) AS ddl_phase,
EventData.value(‘(event/data[@name=”ddl_phase”]/text)[1]’, ‘varchar(10)’) AS ddl_phase_desc,
EventData.value(‘(event/data[@name=”availability_group_name”]/value)[1]’, ‘varchar(25)’) AS availability_group_name,
EventData.value(‘(event/data[@name=”availability_group_id”]/value)[1]’, ‘varchar(36)’) AS availability_group_id,
EventData.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS [statement]
FROM @AOHealth_XELData
WHERE object_name = ‘alwayson_ddl_executed’
AND (EventData.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) LIKE ‘%FAILOVER%’
OR EventData.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) LIKE ‘%FORCE%’)
AND EventData.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) NOT LIKE ‘%CREATE%’
)
SELECT cast(object_name as varchar(22)) AS XEvent, TimeStamp_UTC, ddl_action, ddl_action_desc, ddl_phase,
ddl_phase_desc, availability_group_name, availability_group_id,
CAST(Replace([statement], char(10), ”) as varchar(80)) as [statement]
FROM AODDL
ORDER BY TimeStamp_UTC desc;
END
IF EXISTS(SELECT object_name FROM @AOHealth_XELData WHERE object_name = ‘availability_replica_manager_state_change’)
BEGIN
PRINT ‘Availability Replica Manager state changes’
PRINT ‘==========================================’;
— display results for “availability_replica_manager_state_change” events
SELECT cast(object_name as varchar(42)) AS XEvent, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”current_state”]/value)[1]’, ‘int’) AS current_state,
EventData.value(‘(event/data[@name=”current_state”]/text)[1]’, ‘varchar(30)’) AS current_state_desc
FROM @AOHealth_XELData
WHERE object_name = ‘availability_replica_manager_state_change’
ORDER BY TimeStamp_UTC desc;
END
IF EXISTS(SELECT object_name FROM @AOHealth_XELData WHERE object_name = ‘availability_replica_state’)
BEGIN
PRINT ‘Availability Replica state’
PRINT ‘==========================’;
— display results for “availability_replica_state” events
SELECT cast(object_name as varchar(34)) AS XEvent, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”current_state”]/value)[1]’, ‘int’) AS current_state,
EventData.value(‘(event/data[@name=”current_state”]/text)[1]’, ‘varchar(20)’) AS current_state_desc,
EventData.value(‘(event/data[@name=”availability_group_name”]/value)[1]’, ‘varchar(36)’) AS availability_group_name,
EventData.value(‘(event/data[@name=”availability_group_id”]/value)[1]’, ‘varchar(36)’) AS availability_group_id,
EventData.value(‘(event/data[@name=”availability_replica_id”]/value)[1]’, ‘varchar(36)’) AS availability_replica_id
FROM @AOHealth_XELData
WHERE object_name = ‘availability_replica_state’
ORDER BY TimeStamp_UTC desc;
END
IF EXISTS(SELECT object_name FROM @AOHealth_XELData WHERE object_name = ‘availability_replica_state_change’)
BEGIN
PRINT ‘Availability Replica state changes’
PRINT ‘==================================’;
— display results for “availability_replica_state_change” events
SELECT cast(object_name as varchar(34)) AS XEvent, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
IsNULL(EventData.value(‘(event/data[@name=”availability_replica_name”]/value)[1]’, ‘varchar(25)’), ‘Data Unavailable’) AS availability_replica_name,
EventData.value(‘(event/data[@name=”availability_group_name”]/value)[1]’, ‘varchar(25)’) AS availability_group_name,
EventData.value(‘(event/data[@name=”previous_state”]/value)[1]’, ‘int’) AS previous_state,
EventData.value(‘(event/data[@name=”previous_state”]/text)[1]’, ‘varchar(30)’) AS previous_state_desc,
EventData.value(‘(event/data[@name=”current_state”]/value)[1]’, ‘int’) AS current_state,
EventData.value(‘(event/data[@name=”current_state”]/text)[1]’, ‘varchar(30)’) AS current_state_desc,
EventData.value(‘(event/data[@name=”availability_replica_id”]/value)[1]’, ‘varchar(36)’) AS availability_replica_id,
EventData.value(‘(event/data[@name=”availability_group_id”]/value)[1]’, ‘varchar(36)’) AS availability_group_id
FROM @AOHealth_XELData
WHERE object_name = ‘availability_replica_state_change’
ORDER BY TimeStamp_UTC DESC;
END
IF EXISTS(SELECT object_name FROM @AOHealth_XELData WHERE object_name = ‘availability_group_lease_expired’)
BEGIN
PRINT ‘Lease Expiration Events’
PRINT ‘=======================’;
— Display results “lease expiration” events
SELECT cast(object_name as varchar(33)) AS XEvent, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”availability_group_name”]/value)[1]’, ‘varchar(25)’) AS AGName,
EventData.value(‘(event/data[@name=”availability_group_id”]/value)[1]’, ‘varchar(36)’) AS AG_ID
FROM @AOHealth_XELData
WHERE object_name = ‘availability_group_lease_expired’
ORDER BY TimeStamp_UTC desc;
END
IF EXISTS(SELECT object_name FROM @AOHealth_XELData WHERE object_name = ‘lock_redo_blocked’)
BEGIN
PRINT ‘BLOCKED REDO Events’
PRINT ‘===================’;
— Display results “lock_redo_blocked” events
SELECT cast(object_name as varchar(42)) AS XEvent, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”resource_type”]/value)[1]’, ‘int’) AS ResourceType,
EventData.value(‘(event/data[@name=”resource_type”]/text)[1]’, ‘varchar(25)’) AS ResourceTypeDesc,
EventData.value(‘(event/data[@name=”mode”]/value)[1]’, ‘int’) AS Mode,
EventData.value(‘(event/data[@name=”mode”]/text)[1]’, ‘varchar(25)’) AS ModeDesc,
EventData.value(‘(event/data[@name=”owner_type”]/value)[1]’, ‘int’) AS OwnerType,
EventData.value(‘(event/data[@name=”owner_type”]/text)[1]’, ‘varchar(25)’) AS OwnerTypeDesc,
EventData.value(‘(event/data[@name=”transaction_id”]/value)[1]’, ‘bigint’) AS transaction_id,
EventData.value(‘(event/data[@name=”database_id”]/value)[1]’, ‘int’) AS database_id,
EventData.value(‘(event/data[@name=”lockspace_workspace_id”]/value)[1]’, ‘varchar(22)’) AS lockspace_workspace_id,
EventData.value(‘(event/data[@name=”lockspace_sub_id”]/value)[1]’, ‘bigint’) AS lockspace_sub_id,
EventData.value(‘(event/data[@name=”lockspace_nest_id”]/value)[1]’, ‘bigint’) AS lockspace_nest_id,
EventData.value(‘(event/data[@name=”resource_0″]/value)[1]’, ‘bigint’) AS resource_0,
EventData.value(‘(event/data[@name=”resource_1″]/value)[1]’, ‘bigint’) AS resource_1,
EventData.value(‘(event/data[@name=”resource_2″]/value)[1]’, ‘bigint’) AS resource_2,
EventData.value(‘(event/data[@name=”object_id”]/value)[1]’, ‘bigint’) AS [object_id],
EventData.value(‘(event/data[@name=”associated_object_id”]/value)[1]’, ‘bigint’) AS associated_object_id,
EventData.value(‘(event/data[@name=”duration”]/value)[1]’, ‘int’) AS duration,
EventData.value(‘(event/data[@name=”resource_description”]/value)[1]’, ‘varchar(25)’) AS resource_description
FROM @AOHealth_XELData
WHERE object_name = ‘lock_redo_blocked’
ORDER BY TimeStamp_UTC desc;
END
IF EXISTS(SELECT object_name FROM @AOHealth_XELData WHERE object_name = ‘hadr_db_partner_set_sync_state’)
BEGIN
PRINT ‘hadr_db_partner_set_sync_state Events’
PRINT ‘=====================================’;
— Display results “hadr_db_partner_set_sync_state” events
SELECT cast(object_name as varchar(42)) AS XEvent, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”database_id”]/value)[1]’, ‘int’) AS database_id,
EventData.value(‘(event/data[@name=”commit_policy”]/value)[1]’, ‘int’) AS commit_policy,
EventData.value(‘(event/data[@name=”commit_policy”]/text)[1]’, ‘varchar(20)’) AS commit_policy_desc,
EventData.value(‘(event/data[@name=”commit_policy_target”]/value)[1]’, ‘int’) AS commit_policy_target,
EventData.value(‘(event/data[@name=”commit_policy_target”]/text)[1]’, ‘varchar(20)’) AS commit_policy_target_desc,
EventData.value(‘(event/data[@name=”sync_state”]/value)[1]’, ‘int’) AS sync_state,
EventData.value(‘(event/data[@name=”sync_state”]/text)[1]’, ‘varchar(20)’) AS sync_state_desc,
EventData.value(‘(event/data[@name=”sync_log_block”]/value)[1]’, ‘varchar(20)’) AS sync_log_block,
EventData.value(‘(event/data[@name=”group_id”]/value)[1]’, ‘varchar(36)’) AS group_id,
EventData.value(‘(event/data[@name=”replica_id”]/value)[1]’, ‘varchar(36)’) AS replica_id,
EventData.value(‘(event/data[@name=”ag_database_id”]/value)[1]’, ‘varchar(36)’) AS ag_database_id
FROM @AOHealth_XELData
WHERE object_name = ‘hadr_db_partner_set_sync_state’
ORDER BY TimeStamp_UTC desc;
END
IF EXISTS(SELECT object_name FROM @AOHealth_XELData WHERE object_name = ‘availability_replica_automatic_failover_validation’)
BEGIN
PRINT ‘availability_replica_automatic_failover_validation’
PRINT ‘==================================================’;
— Display results “availability_replica_automatic_failover_validation” events
SELECT cast(object_name as varchar(50)) AS XEvent, CASE WHEN
ISDATE(EventData.value(‘(event/@timestamp)[1]’, ‘varchar(25)’) ) =0
THEN NULL
ELSE CAST(EventData.value(‘(event/@timestamp)[1]’, ‘datetimeoffset(2)’) as DATETIMEOFFSET(2))
END
AS TimeStamp_UTC,
EventData.value(‘(event/data[@name=”availability_replica_name”]/value)[1]’, ‘varchar(25)’) AS availability_replica_name,
EventData.value(‘(event/data[@name=”availability_group_name”]/value)[1]’, ‘varchar(25)’) AS availability_group_name,
EventData.value(‘(event/data[@name=”availability_replica_id”]/value)[1]’, ‘varchar(36)’) AS availability_replica_id,
EventData.value(‘(event/data[@name=”availability_group_id”]/value)[1]’, ‘varchar(36)’) AS availability_group_id,
EventData.value(‘(event/data[@name=”forced_quorum”]/value)[1]’, ‘varchar(5)’) AS forced_quorum,
EventData.value(‘(event/data[@name=”joined_and_synchronized”]/value)[1]’, ‘varchar(5)’) AS joined_and_synchronized,
EventData.value(‘(event/data[@name=”previous_primary_or_automatic_failover_target”]/value)[1]’, ‘varchar(5)’) AS previous_primary_or_automatic_failover_target
FROM @AOHealth_XELData
WHERE object_name = ‘availability_replica_automatic_failover_validation’
ORDER BY TimeStamp_UTC desc;
END
DECLARE @AOHealthSummary TABLE –CREATE TABLE @AOHealthSummary
(XEvent varchar(50), [COUNT] INT);
INSERT INTO @AOHealthSummary
SELECT CAST(xv.event_name AS VARCHAR(50)), 0
FROM sys.dm_xe_sessions xes
INNER JOIN sys.dm_xe_session_events xv ON xes.address = xv.event_session_address
WHERE xes.name like ‘AlwaysOn_Health’
ORDER BY event_name;
With Summary (XEvent, [Count])
AS (SELECT CAST(object_name AS VARCHAR(50)) AS [XEvent], count(*) AS [Count]
FROM @AOHealth_XELData
GROUP BY object_name)
UPDATE @AOHealthSummary
SET [COUNT] = s.[COUNT]
FROM Summary s
INNER JOIN @AOHealthSummary ao ON s.XEvent = ao.XEvent;
IF EXISTS(SELECT * FROM @AOHealthSummary) BEGIN
PRINT ‘Summary event counts for AO Health XEvents’
PRINT ‘==========================================’;
— Display event counts for AO Health XEvent data
SELECT * FROM @AOHealthSummary
ORDER BY [count] DESC, XEvent
END
GO
Leave a Reply