Last active
April 27, 2023 20:27
-
-
Save mbourgon/5bd2d9c2456b2c613d8ccf36e403e98a to your computer and use it in GitHub Desktop.
severity_10plus_errors_XE_memory
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--1.1, 20191210 first version I know of | |
--1.2, 20230223 major changes to speed it up, after dealing with a particular server, added Azure SQL DB details. | |
--1.21 20230403 adding event_sequence | |
--1.22 20230427 adding CPID Client PID. Find app with posh: gwmi win32_process -cn theclientnamehere -filter "ProcessId=2900" | |
--thebakingdba.blogspot.com | |
/* | |
CREATE EVENT SESSION | |
severity_10plus_errors_XE_memory | |
ON server --use "on database" for Azure SQL DB | |
ADD EVENT sqlserver.error_reported | |
( | |
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username,sqlserver.client_pid) | |
--ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.username) | |
WHERE ([severity]> 10) | |
) | |
ADD TARGET | |
package0.ring_buffer | |
(SET | |
max_memory = 10000 ) -- Units of KB. | |
WITH (MAX_DISPATCH_LATENCY = 60SECONDS,STARTUP_STATE = on) | |
GO | |
ALTER EVENT SESSION severity_10plus_errors_XE_memory | |
ON server --use "on database" for Azure SQL DB | |
STATE = START; | |
GO | |
*/ | |
if object_id('tempdb..#xevent') is not null | |
DROP TABLE #xevent | |
CREATE TABLE #xevent (target_data XML) | |
INSERT INTO #xevent (target_data) | |
SELECT target_data | |
FROM sys.dm_xe_session_targets AS st --For Azure SQL DB, use dm_xe_database_session_targets | |
INNER JOIN sys.dm_xe_sessions AS se --For Azure SQL DB, use dm_xe_database_sessions | |
ON CAST(se.address AS BINARY(8)) = CAST(st.event_session_address AS BINARY(8)) | |
WHERE | |
se.name = 'severity_10plus_errors_XE_memory' AND st.target_name = 'ring_buffer' | |
DECLARE @count VARCHAR(10) | |
SELECT @count = target_data.value('/RingBufferTarget[1]/@eventCount','varchar(10)') | |
FROM #xevent --null means it's not on or not "installed" | |
PRINT 'eventCount = ' + @count | |
--need two temp tables to speed shredding; this one will hold one row per event. | |
if object_id('tempdb..#xevent2') is not null | |
DROP TABLE #xevent2 | |
CREATE TABLE #xevent2 (err_timestamp DATETIME2, target_data XML) | |
CREATE INDEX ncidx__xevent2__err_timestamp ON #xevent2(err_timestamp) | |
--shredded-to-node for speed and to filter | |
INSERT INTO #xevent2 | |
SELECT DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), | |
ed.c.value('(@timestamp)[1]', 'nvarchar(max)')) AS err_timestamp, ed.c.query('.') AS event_data | |
FROM #xevent a | |
cross apply (select CAST(target_data as XML) as event_data) as xevents | |
CROSS APPLY xevents.event_data.nodes('RingBufferTarget/event') ed(c) | |
--final select out. Put filters in the SELECT * WHERE clause | |
;with events_cte as( | |
select err_timestamp, | |
target_data.value('(/event/data[@name="severity"]/value)[1]', 'bigint') AS [err_severity], | |
target_data.value('(/event/data[@name="error_number"]/value)[1]', 'bigint') AS [err_number], | |
target_data.value('(/event/data[@name="message"]/value)[1]', 'nvarchar(512)') AS [err_message], | |
target_data.value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text], | |
target_data.value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS client_app_name, | |
target_data.value('(/event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS client_hostname, | |
target_data.value('(/event/action[@name="database_id"]/value)[1]', 'int') AS database_id, | |
target_data.value('(/event/action[@name="nt_username"]/value)[1]', 'nvarchar(max)') AS nt_username, | |
target_data.value('(/event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS username, | |
target_data.value('(/event/action[@name="session_nt_username"]/value)[1]', 'nvarchar(max)') AS session_nt_username, | |
target_data.value('(/event/action[@name="event_sequence"]/value)[1]', 'nvarchar(max)') AS event_sequence, | |
target_data.value('(/event/action[@name="client_pid"]/value)[1]', 'int') AS client_pid | |
, a.target_data AS event_data --we can leave this off since it makes this run SO MUCH LONGER. | |
FROM #xevent2 a | |
) | |
SELECT events_cte.[err_timestamp], events_cte.[err_severity], events_cte.[err_number], events_cte.[err_message], events_cte.[sql_text], events_cte.client_app_name, events_cte.client_hostname | |
, client_pid, DB_NAME(events_cte.database_id) as database_name, events_cte.nt_username, username, events_cte.session_nt_username, events_cte.event_sequence | |
, events_cte.event_data | |
from events_cte | |
WHERE | |
NOT (events_cte.err_number = 17830 AND events_cte.err_severity = 20) --that network error disconnect message | |
--and (events_cte.client_hostname IS NULL OR events_cte.client_hostname not IN ('thebakingdba')) | |
--and err_timestamp >='20210930' | |
order by err_timestamp desc; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment