Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active December 5, 2021 12:14
Show Gist options
  • Save EitanBlumin/bcb3b5a8a7cbeb7756838b0330120668 to your computer and use it in GitHub Desktop.
Save EitanBlumin/bcb3b5a8a7cbeb7756838b0330120668 to your computer and use it in GitHub Desktop.
Collect T-SQL Events using an Extended Events Buffer
-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 2020-05-31
-- Last Update: 2021-04-22
-- Description: Collect T-SQL Events using an Extended Events Buffer
SET NOCOUNT ON;
DECLARE
@SourceLinkedServer SYSNAME
, @MinimumDurationMilliSeconds BIGINT
, @CaptureAllTimeoutsOrAborts BIT
, @BufferMaxMemoryMB INT
, @BufferMaxEventsCount INT
, @BufferXESessionName SYSNAME
, @UseFileTarget BIT
, @FileTargetMaxFiles INT
, @FileTargetMaxFileSizeMB INT
, @FileTargetPath NVARCHAR(4000)
, @FlushBuffer BIT
, @PrintOnly BIT
, @PreferObjectName BIT
DECLARE @ProgramsToIgnore AS TABLE(appname SYSNAME);
DECLARE @ProceduresToIgnore AS TABLE(procname SYSNAME);
/********************* CONFIGURATION *********************/
SET @BufferXESessionName = 'TSQLEventsCollector' -- Name of the extended events session
SET @SourceLinkedServer = NULL -- Optionally place a linked server name here. Set as NULL to monitor the local server.
SET @MinimumDurationMilliSeconds= 3000 -- Set minimum threshold of event duration to capture
SET @CaptureAllTimeoutsOrAborts = 1 -- If set to 1, will also capture timeout and SQL error events
SET @PrintOnly = 0 -- Set to 1 to get the command in text as output, instead of running it
SET @UseFileTarget = 1 -- Set to 1 to use file target, otherwise, the ring buffer will be used
SET @PreferObjectName = 1 -- If set to 1, will prefer to output a procedure's object name rather than the SQL batch text
/*************** Ring Buffer Configuration ***************/
SET @FlushBuffer = 1 -- Set to 1 to flush the ring buffer between executions by recreating the XE session. Otherwise, keep data.
SET @BufferMaxMemoryMB = 16 -- Max memory in MB for the ring buffer
SET @BufferMaxEventsCount = 50000 -- Max number of events to hold between flushes
/*************** File Target Configuration ***************/
SET @FileTargetPath = NULL -- Set the file target path (required in Azure SQL DB to use Blob Storage URL). Leave as NULL to use default SQL Server LOG folder.
SET @FileTargetMaxFiles = 5 -- Max number of rollover file target files
SET @FileTargetMaxFileSizeMB = 20 -- Max size in MB of each file target file
/****************** Programs to Exclude ******************/
INSERT INTO @ProgramsToIgnore
SELECT 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
UNION ALL SELECT 'Microsoft SQL Server Management Studio - Query'
UNION ALL SELECT 'Microsoft SQL Server Management Studio'
UNION ALL SELECT 'SQLServerCEIP'
UNION ALL SELECT 'check_mssql_health'
UNION ALL SELECT 'SQL Server Performance Investigator'
/***************** Procedures to Exclude *****************/
INSERT INTO @ProceduresToIgnore
SELECT 'sp_reset_connection'
--UNION ALL SELECT 'sp_executesql'
/********************* /CONFIGURATION *********************/
DECLARE @CMD NVARCHAR(MAX), @Filters NVARCHAR(MAX), @ProcFilters NVARCHAR(MAX), @Executor NVARCHAR(1000)
DECLARE @IsAzureSQLDB BIT, @IsNestedTransaction BIT
SET @Executor = ISNULL(QUOTENAME(@SourceLinkedServer) + N'...', N'') + N'sp_executesql'
SET @CMD = N'SET @IsAzureSQLDB = CASE WHEN SERVERPROPERTY(''Edition'') = ''SQL Azure'' THEN 1 ELSE 0 END;'
EXEC @Executor @CMD, N'@IsAzureSQLDB BIT OUTPUT', @IsAzureSQLDB OUTPUT;
IF @IsAzureSQLDB = 1 AND @UseFileTarget = 1 AND (@FileTargetPath IS NULL OR @FileTargetPath NOT LIKE 'https://%')
BEGIN
RAISERROR(N'When using File Target in Azure SQL DB, you must specify a valid Azure Blog Storage URL in @FileTargetPath.',11,1);
RAISERROR('Switching over to ring buffer target.',0,1) WITH NOWAIT;
SET @UseFileTarget = 0;
END
-- Add xel file postfix if missing
IF @FileTargetPath IS NOT NULL AND @UseFileTarget = 1
BEGIN
IF RIGHT(@FileTargetPath, 1) IN ('/','\') SET @FileTargetPath = @FileTargetPath + @BufferXESessionName + '.xel'
IF RIGHT(@FileTargetPath, 4) <> '.xel' SET @FileTargetPath = @FileTargetPath + '.xel'
END
IF @UseFileTarget = 1 AND @FlushBuffer = 1
PRINT N'WARNING: Using File Target. Buffer will NOT be flushed!'
ELSE
SET @UseFileTarget = ISNULL(@UseFileTarget, 0);
SET @Filters = N'([package0].[equal_boolean]([sqlserver].[is_system],(0)))
AND (' + CASE WHEN @CaptureAllTimeoutsOrAborts = 1 THEN N'result = 2 OR ' ELSE N'' END + N'duration >= ' + CONVERT(nvarchar, @MinimumDurationMilliSeconds * 1000) + N')'
SELECT @Filters = @Filters + CHAR(10) + N'AND (sqlserver.client_app_name <> ' + QUOTENAME(appname, N'''') + N')'
FROM @ProgramsToIgnore
SELECT @ProcFilters = ISNULL(@ProcFilters, N'') + CHAR(10) + N'AND (object_name <> ''' + REPLACE(procname, N'''', N'''''') + N''')'
FROM @ProceduresToIgnore
SetUpCommand:
IF @IsNestedTransaction = 1 OR (@IsAzureSQLDB = 1 AND @SourceLinkedServer IS NULL)
BEGIN
SET @PrintOnly = 1
RAISERROR(N'This database version is not supported for dynamic execution. Please take the generated script in attached results instead and run it directly.'
, 10, 0);
END
IF @PrintOnly = 1
BEGIN
SET @CMD = N'-- Retrieve buffer contents
IF OBJECT_ID(''tempdb..#events'') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
INSERT INTO #events
' + CASE WHEN @UseFileTarget = 1 THEN
N'SELECT xdata = CAST(event_data AS xml)
FROM (
select [TargetFileName] = REPLACE(c.column_value, ''.xel'', ''*.xel'')
from sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS s
join sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_object_columns AS c ON s.address = c.event_session_address
where column_name = ''filename'' and s.name = ' + QUOTENAME(@BufferXESessionName, N'''') + N'
) AS FileTarget CROSS APPLY sys.fn_xe_file_target_read_file (FileTarget.TargetFileName,null,null, null)'
ELSE
N'SELECT X.query(''.'')
FROM (SELECT xdata = CAST(xet.target_data AS xml)
FROM sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_targets AS xet
JOIN sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS xe ON xe.address = xet.event_session_address
WHERE xe.name = ' + QUOTENAME(@BufferXESessionName, N'''') + N'
AND target_name= ''ring_buffer''
) AS a
CROSS APPLY xdata.nodes (N''//event'') AS session_events (X)'
END + N'
-- Unfurl raw data
SELECT
[server_name] = @@SERVERNAME,
[database_name] = session_events.event_xml.value (N''(event/action[@name="database_name"]/value)[1]'' , N''SYSNAME'') ,
event_name = session_events.event_xml.value (N''(event/@name)[1]'' , N''NVARCHAR(1000)'') ,
event_timestamp_utc = session_events.event_xml.value (N''(event/@timestamp)[1]'' , N''DATETIME2(7)'') ,
session_id = session_events.event_xml.value (N''(event/action[@name="session_id"]/value)[1]'' , N''BIGINT'') ,
cpu_time = session_events.event_xml.value (N''(event/data[@name="cpu_time"]/value)[1]'' , N''BIGINT'') ,
duration = session_events.event_xml.value (N''(event/data[@name="duration"]/value)[1]'' , N''BIGINT'') ,
physical_reads = session_events.event_xml.value (N''(event/data[@name="physical_reads"]/value)[1]'' , N''BIGINT'') ,
logical_reads = session_events.event_xml.value (N''(event/data[@name="logical_reads"]/value)[1]'' , N''BIGINT'') ,
writes = session_events.event_xml.value (N''(event/data[@name="writes"]/value)[1]'' , N''BIGINT'') ,
row_count = session_events.event_xml.value (N''(event/data[@name="row_count"]/value)[1]'' , N''BIGINT'') ,
result = session_events.event_xml.value (N''(event/data[@name="result"]/value)[1]'' , N''INT'') ,
result_desc = session_events.event_xml.value (N''(event/data[@name="result"]/text)[1]'' , N''VARCHAR(15)'') ,
client_app_name = session_events.event_xml.value (N''(event/action[@name="client_app_name"]/value)[1]'' , N''NVARCHAR(1000)'') ,
client_host_name = session_events.event_xml.value (N''(event/action[@name="client_hostname"]/value)[1]'' , N''NVARCHAR(1000)'') ,
client_process_id = session_events.event_xml.value (N''(event/action[@name="client_pid"]/value)[1]'' , N''BIGINT'') ,
username = session_events.event_xml.value (N''(event/action[@name="username"]/value)[1]'' , N''SYSNAME'') ,
plan_handle = session_events.event_xml.value (N''(event/action[@name="plan_handle"]/value)[1]'' , N''VARBINARY(MAX)'') ,
query_plan_hash = session_events.event_xml.value (N''(event/action[@name="query_plan_hash"]/value)[1]'' , N''VARBINARY(MAX)'') ,
sql_text = COALESCE(' + CASE WHEN @PreferObjectName = 1 THEN
N'
session_events.event_xml.value (N''(event/data[@name="object_name"]/value)[1]'' , N''NVARCHAR(MAX)''),
session_events.event_xml.value (N''(event/action[@name="sql_text"]/value)[1]'' , N''NVARCHAR(MAX)''),
session_events.event_xml.value (N''(event/data[@name="statement"]/value)[1]'' , N''NVARCHAR(MAX)'')'
ELSE
N'
session_events.event_xml.value (N''(event/action[@name="sql_text"]/value)[1]'' , N''NVARCHAR(MAX)''),
session_events.event_xml.value (N''(event/data[@name="statement"]/value)[1]'' , N''NVARCHAR(MAX)''),
session_events.event_xml.value (N''(event/data[@name="object_name"]/value)[1]'' , N''NVARCHAR(MAX)'')'
END + N')
FROM #events AS session_events;
'
END
ELSE
BEGIN
SET @CMD = N'-- Retrieve buffer contents
' + CASE WHEN @UseFileTarget = 1 THEN
N'SELECT xdata = CAST(event_data AS varbinary(max))
FROM (
select [TargetFileName] = REPLACE(c.column_value, ''.xel'', ''*.xel'')
from sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS s
join sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_object_columns AS c ON s.address = c.event_session_address
where column_name = ''filename'' and s.name = ' + QUOTENAME(@BufferXESessionName, N'''') + N'
) AS FileTarget CROSS APPLY sys.fn_xe_file_target_read_file (FileTarget.TargetFileName,null,null, null)'
ELSE
N'SELECT xdata = CAST(xet.target_data AS varbinary(max))
FROM sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_targets AS xet
JOIN sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS xe ON xe.address = xet.event_session_address
WHERE xe.name = ' + QUOTENAME(@BufferXESessionName, N'''') + N'
AND target_name= ''ring_buffer'''
END
END
IF @FlushBuffer = 1
BEGIN
SET @CMD = @CMD + N'
-- Recreate session to flush buffer
IF EXISTS (SELECT * FROM sys.' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database' ELSE N'server' END + N'_event_sessions WHERE name = ''' + @BufferXESessionName + N''')
BEGIN
DROP EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N';
END
'
END
SET @CMD = @CMD + N'
IF NOT EXISTS (SELECT * FROM sys.' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database' ELSE N'server' END + N'_event_sessions WHERE name = ''' + @BufferXESessionName + N''')
BEGIN
-- Create the event session
CREATE EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N'
ADD EVENT sqlserver.rpc_completed(
ACTION(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.database_name,
sqlserver.username,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle,
sqlserver.query_plan_hash
)
WHERE ' + @Filters + ISNULL(@ProcFilters, N'') + N'
)
,ADD EVENT sqlserver.sql_batch_completed(
SET collect_batch_text = 1
ACTION(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.database_name,
sqlserver.username,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle,
sqlserver.query_plan_hash
)
WHERE ' + @Filters + N'
)
' + CASE WHEN @UseFileTarget = 1 THEN
CONCAT(
N'ADD TARGET package0.event_file(SET filename=N', QUOTENAME(ISNULL(@FileTargetPath, @BufferXESessionName + '.xel'), N'''')
, N',max_file_size=(', @FileTargetMaxFileSizeMB , N'),max_rollover_files=(', @FileTargetMaxFiles, N'))'
)
ELSE
CONCAT(
N'ADD TARGET package0.ring_buffer(SET max_events_limit=(', @BufferMaxEventsCount, N'),max_memory=(', (@BufferMaxMemoryMB*1024), N'))'
)
END + N'
WITH (MAX_MEMORY=' + CONVERT(nvarchar, @BufferMaxMemoryMB) + N' MB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
;
END
-- Start the event session
IF NOT EXISTS (SELECT * FROM sys.dm_xe' + CASE WHEN @IsAzureSQLDB = 1 THEN N'_database' ELSE N'' END + N'_sessions WHERE name = ''' + @BufferXESessionName + N''')
BEGIN
ALTER EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N' STATE = START;
END'
IF @PrintOnly = 1
BEGIN
SELECT @CMD AS GeneratedScript
END
ELSE
BEGIN
IF OBJECT_ID('tempdb..#xe') IS NOT NULL DROP TABLE #xe;
CREATE TABLE #xe (xdata VARBINARY(MAX));
BEGIN TRY
-- Query the event session data
INSERT INTO #xe
EXEC @Executor @CMD
END TRY
BEGIN CATCH
DECLARE @ERRNum INT = ERROR_NUMBER()
PRINT CONCAT(N'Error ', @ERRNum, N', Line ', ERROR_LINE(), N': ', ERROR_MESSAGE());
-- Error 574: statement cannot be used inside a user transaction.
IF @ERRNum = 574
BEGIN
SET @PrintOnly = 1;
SET @IsNestedTransaction = 1;
GOTO SetUpCommand;
END
END CATCH
IF OBJECT_ID('tempdb..#events') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
IF @UseFileTarget = 1
INSERT INTO #events
SELECT xdata = CONVERT(xml, xdata) FROM #xe
ELSE
INSERT INTO #events
SELECT X.query('.')
FROM (SELECT xdata = CONVERT(xml, xdata) FROM #xe) AS a
CROSS APPLY xdata.nodes (N'//event') AS session_events (X)
-- Unfurl raw data
SELECT
[server_name] = @@SERVERNAME,
[database_name] = session_events.event_xml.value (N'(event/action[@name="database_name"]/value)[1]' , N'SYSNAME') ,
event_name = session_events.event_xml.value (N'(event/@name)[1]' , N'NVARCHAR(1000)') ,
event_timestamp_utc = session_events.event_xml.value (N'(event/@timestamp)[1]' , N'DATETIME2(7)') ,
session_id = session_events.event_xml.value (N'(event/action[@name="session_id"]/value)[1]' , N'BIGINT') ,
cpu_time = session_events.event_xml.value (N'(event/data[@name="cpu_time"]/value)[1]' , N'BIGINT') ,
duration = session_events.event_xml.value (N'(event/data[@name="duration"]/value)[1]' , N'BIGINT') ,
physical_reads = session_events.event_xml.value (N'(event/data[@name="physical_reads"]/value)[1]' , N'BIGINT') ,
logical_reads = session_events.event_xml.value (N'(event/data[@name="logical_reads"]/value)[1]' , N'BIGINT') ,
writes = session_events.event_xml.value (N'(event/data[@name="writes"]/value)[1]' , N'BIGINT') ,
row_count = session_events.event_xml.value (N'(event/data[@name="row_count"]/value)[1]' , N'BIGINT') ,
result = session_events.event_xml.value (N'(event/data[@name="result"]/value)[1]' , N'INT') ,
result_desc = session_events.event_xml.value (N'(event/data[@name="result"]/text)[1]' , N'VARCHAR(15)') ,
client_app_name = session_events.event_xml.value (N'(event/action[@name="client_app_name"]/value)[1]' , N'NVARCHAR(1000)') ,
client_host_name = session_events.event_xml.value (N'(event/action[@name="client_hostname"]/value)[1]' , N'NVARCHAR(1000)') ,
client_process_id = session_events.event_xml.value (N'(event/action[@name="client_pid"]/value)[1]' , N'BIGINT') ,
username = session_events.event_xml.value (N'(event/action[@name="username"]/value)[1]' , N'SYSNAME') ,
plan_handle = session_events.event_xml.value (N'(event/action[@name="plan_handle"]/value)[1]' , N'VARBINARY(MAX)') ,
query_plan_hash = session_events.event_xml.value (N'(event/action[@name="query_plan_hash"]/value)[1]' , N'VARBINARY(MAX)') ,
sql_text = CASE WHEN @PreferObjectName = 1 THEN
COALESCE(
session_events.event_xml.value (N'(event/data[@name="object_name"]/value)[1]' , N'NVARCHAR(MAX)'),
session_events.event_xml.value (N'(event/action[@name="sql_text"]/value)[1]' , N'NVARCHAR(MAX)'),
session_events.event_xml.value (N'(event/data[@name="statement"]/value)[1]' , N'NVARCHAR(MAX)')
)
ELSE
COALESCE(
session_events.event_xml.value (N'(event/action[@name="sql_text"]/value)[1]' , N'NVARCHAR(MAX)'),
session_events.event_xml.value (N'(event/data[@name="statement"]/value)[1]' , N'NVARCHAR(MAX)'),
session_events.event_xml.value (N'(event/data[@name="object_name"]/value)[1]' , N'NVARCHAR(MAX)')
)
END
FROM #events AS session_events;
DROP TABLE #events;
END
@behnam-io
Copy link

Getting this error when running the script on a local SQL server

Error 574, Line 15: CREATE EVENT SESSION statement cannot be used inside a user transaction.

@EitanBlumin
Copy link
Author

Hi @behnam-io, thanks for the heads up.
This error happens in Azure SQL DB and newer versions of SQL Server.
It basically means that it's impossible to run CREATE/DROP EVENT SESSION commands using dynamic/nested SQL.

I modified the code a bit so that if such an error occurs, it will output in the Results pane the script that it tried to execute.
You will have to take the generated script from the Results pane and run it separately.

@behnam-io
Copy link

@EitanBlumin Thanks for the fast reponse and fix. there was another error when used the generated result with the ""s. Worked after replacing them with single "

@EitanBlumin
Copy link
Author

EitanBlumin commented Apr 22, 2021

@behnam-io That could've been caused by a formatting issue in GitHub.
Have you tried copying from the Raw version?

@behnam-io
Copy link

Yes, the code is actually file. Just the result which comes in double quotes, the escaped double-quotes inside the string are extra, replaced the scaped double-quote with single double-quote and it worked like a charm :)

@behnam-io
Copy link

Sending the query result as-is, so it will be clear for you. Check that ""database_name"" for example:

"-- Retrieve buffer contents
IF OBJECT_ID('tempdb..#events') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
INSERT INTO #events
SELECT xdata = CAST(event_data AS xml)
FROM (
select [TargetFileName] = REPLACE(c.column_value, '.xel', '*.xel')
from sys.dm_xe_sessions AS s
join sys.dm_xe_session_object_columns AS c ON s.address = c.event_session_address
where column_name = 'filename' and s.name = 'CaptureTSQLErrors'
) AS FileTarget CROSS APPLY sys.fn_xe_file_target_read_file (FileTarget.TargetFileName,null,null, null)
-- Unfurl raw data
SELECT
[server_name]  = @@SERVERNAME,
[database_name]  = session_events.event_xml.value (N'(event/action[@name=""database_name""]/value)[1]' , N'SYSNAME') ,
event_name  = session_events.event_xml.value (N'(event/@name)[1]' , N'NVARCHAR(1000)') ,
event_timestamp_utc = session_events.event_xml.value (N'(event/@timestamp)[1]' , N'DATETIME2(7)') ,
session_id  = session_events.event_xml.value (N'(event/action[@name=""session_id""]/value)[1]' , N'INT') ,
error_number  = session_events.event_xml.value (N'(event/data[@name=""error_number""]/value)[1]' , N'INT') ,
severity  = session_events.event_xml.value (N'(event/data[@name=""severity""]/value)[1]' , N'INT') ,
state   = session_events.event_xml.value (N'(event/data[@name=""state""]/value)[1]' , N'INT') ,
category  = session_events.event_xml.value (N'(event/data[@name=""category""]/value)[1]' , N'INT') ,
category_desc  = session_events.event_xml.value (N'(event/data[@name=""category""]/text)[1]' , N'NVARCHAR(MAX)') ,
message   = session_events.event_xml.value (N'(event/data[@name=""message""]/value)[1]' , N'NVARCHAR(MAX)') ,
client_app_name  = session_events.event_xml.value (N'(event/action[@name=""client_app_name""]/value)[1]' , N'NVARCHAR(1000)') ,
client_host_name = session_events.event_xml.value (N'(event/action[@name=""client_hostname""]/value)[1]' , N'NVARCHAR(1000)') ,
client_process_id = session_events.event_xml.value (N'(event/action[@name=""client_pid""]/value)[1]' , N'BIGINT') ,
username  = session_events.event_xml.value (N'(event/action[@name=""username""]/value)[1]' , N'SYSNAME') ,
sql_text  = session_events.event_xml.value (N'(event/action[@name=""sql_text""]/value)[1]' , N'NVARCHAR(MAX)')
,event_xml
FROM #events AS session_events;

-- Recreate session to flush buffer
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'CaptureTSQLErrors')
BEGIN
DROP EVENT SESSION [CaptureTSQLErrors] ON SERVER;
END

IF NOT EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'CaptureTSQLErrors')
BEGIN
-- Create the event session
CREATE EVENT SESSION [CaptureTSQLErrors] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.username,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.sql_text
)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))
AND [severity]>=(11) AND [sqlserver].[sql_text]<>N''

AND (sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
AND (sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Query')
AND (sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio')
AND (sqlserver.client_app_name <> 'SQLServerCEIP')
AND (sqlserver.client_app_name <> 'check_mssql_health')
AND (sqlserver.client_app_name <> 'SQL Server Performance Investigator')
)
ADD TARGET package0.event_file(SET filename=N'CaptureTSQLErrors.xel',max_file_size=(20),max_rollover_files=(5))
WITH (MAX_MEMORY=16 MB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
;
END
-- Start the event session
IF NOT EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE name = 'CaptureTSQLErrors')
BEGIN
ALTER EVENT SESSION [CaptureTSQLErrors] ON SERVER STATE = START;
END"

@EitanBlumin
Copy link
Author

Yes, I can see it.
However, there's nothing in my code that could cause this.
Perhaps you're using some kind of non-standard way of retrieving the results?

Azure Data Studio? Powershell? Some kind of SSMS plugin?

Either way, it seems to be something specific to your environment or workstation.

@behnam-io
Copy link

@EitanBlumin, btw was changing the scope of this gist from logging query results to error logging intentional? The query and result is not in accord to the title, now it's same as another gist -> https://gist.github.com/EitanBlumin/cc804945d3fbd9629309c5cdb46b0d63

@EitanBlumin
Copy link
Author

@behnam-io oh damn, you're right!
I didn't notice.

I apologize. I updated it now. Please check if it works for you.

@behnam-io
Copy link

@EitanBlumin. Line 183 and 184 (the RaiseError) prevents the text output, but commenting that was enough for me to move on :). With a little Text modification it worked for me.

Thanks for the good job and editing :), really 🤗

I apologize. I updated it now. Please check if it works for you.

בבקשה. Happens ^__^, guess that was because the joy and happiness for the end of Corona in your country 😸

@EitanBlumin
Copy link
Author

בבקשה. Happens ^__^, guess that was because the joy and happiness for the end of Corona in your country 😸

@behnam-io hahaha! that just might be it 😅

I updated the code again, this time based on the more polished code of the error capture script.
It should get rid of the error message as well.

@behnam-io
Copy link

@EitanBlumin Great, you saved me days. תודה

@behnam-io
Copy link

behnam-io commented Dec 5, 2021

Hello @EitanBlumin. I'm still thankful of for this great script, just one question recently comes to my mind. Isn't there any risk to miss data between session drop and creation iterations? I use this as a 5-min cycle iteration in my sql server, I double whether there can be some data miss between collection processes

@EitanBlumin
Copy link
Author

EitanBlumin commented Dec 5, 2021

Hi @behnam-io !

I understand what you're asking. The script, however, drops and then immediately re-creates the session. So, it's very unlikely that something would be missed by it - unless your system is extremely high throughput. As in, multiple commands per millisecond.

I guess there's always such a risk and it's a matter of probability. But, as I said, it's highly unlikely to happen (at least in most environments, anyway).

@behnam-io
Copy link

behnam-io commented Dec 5, 2021

@EitanBlumin I'm working with this in a very baaaad environment :). multiple simultaneous commands. I wonder if it's possible to overcome or atleast mitigate this a bit more by any trick? Maybe using transactions or something. The environment I use this can't accept data miss, but is fine with dirty reads or locks under 1s

@EitanBlumin
Copy link
Author

hmm... I guess you could try creating a new events session with a different name before dropping the old one.

And then on the next execution do the same but the other way around, alternating between the two event session names?

@behnam-io
Copy link

@EitanBlumin Yes, maybe. I tested using transaction but it doesn't allow using CREATE Session in a user transaction

@EitanBlumin
Copy link
Author

using a transaction? why would you need a transaction for creating an extended events session?

@behnam-io
Copy link

@EitanBlumin was just a dumb speculation :)))). My bad

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment