June 17, 2020 at 2:04 am
Per another thread, I created an Extended Event to capture SQL activity. But now I want to read the file and find specific event.
I ran "SP_WHO2", then wanted to find it. I found this code.
select * from sys.fn_xe_file_target_read_file('D:\SQL Server 2016 SP2\TraceFiles\SQL_Activity_EE_0_132368306610620000.xel', null, null, null)
where event_data like '%sp_who2%'
For some reason I get 21 results looking for SP_WHO2, even though I ran it once. And it's not very readable.
Is there a way to format it, similar to how I would look at Trace results, when I would run this ?
SELECT tr.starttime ,name as 'DB_Name', *
FROM fn_trace_gettable('D:\SQL Server 2016 SP2\TraceFiles\MyDMLtrace_SQL_x200612_x109_2.trc', 1) tr
join master.sys.databases on tr.databaseid = database_id
where textdata like '%sp_who2%'
June 17, 2020 at 2:38 am
My Extended event is below. I was originally including "sp_statement_completed", but that seemed to grow the file about 1 gig every few minutes. Obviously new at E.E.
CREATE EVENT SESSION [SQL_Activity] ON SERVER
ADD EVENT sqlserver.cursor_execute(
ACTION(package0.callstack,package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)),
ADD EVENT sqlserver.login(
ACTION(package0.callstack,package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)),
ADD EVENT sqlserver.login_event(
ACTION(package0.callstack,package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.callstack,package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)),
ADD EVENT sqlserver.sql_statement_completed(
ACTION(package0.callstack,package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'D:\SQL Server 2016 SP2\TraceFiles\SQL_Activity_EE.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
June 17, 2020 at 12:37 pm
How do I parse the "Event_Data" column so I can query it based on user, date, text string search etc ?
June 18, 2020 at 1:11 pm
It's just a question of using XQuery to pull it out. Here's an example from my blog on how to do it. Here's another example doing the same thing a little differently. Parse out the XML and you can do anything you like. Another option is to open the data in the Live Data Explorer window and use that tool. Here are several posts that show how to use it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply