I'm attempting to use XEvents to find out which queries are causing my templog.ldf file to grow in size. I'm getting events, but I see the sizes of the files haven't changed, and that the
sql_text field is empty.
What I'd like to do is filter my Event so that only events containing a non-null
sql_text with a LEN() > 0 are logged. However, I'm not sure how to do this. When I add the term
sqlserver.sql_text IS NOT NULL to my filter, I get syntax highlighting that something is wrong.
ACTION(...sqlserver.sql_text) WHERE ( ...AND sqlserver.sql_text IS NOT NULL )
So how do I use something like
[sqlserver].[not_equal_i_sql_unicode_string] to filter out NULLs? And what is the difference between
[sqlserver].[not_equal_sql_unicode_string] and the other variants?
Any help is greatly appreciated.
EDIT: Below is the XEvent scrip that I used to spy on the statements that were causing my tempdb to grow.
CREATE EVENT SESSION [TempDbGrowth3] ON SERVER ADD EVENT sqlserver.database_file_size_change( ACTION(sqlserver.client_hostname, sqlserver.database_id, sqlserver.session_id, sqlserver.sql_text) WHERE ([database_id]=(2) AND [session_id]>(50))), ADD EVENT sqlserver.databases_log_file_used_size_changed( ACTION(sqlserver.client_hostname, sqlserver.database_id, sqlserver.session_id, sqlserver.sql_text) WHERE ([database_id]=(2) AND [session_id]>(50))) ADD TARGET package0.event_file(SET filename=N'C:\Temp\TempDbGrowth3.xel', max_file_size=(10),max_rollover_files=(10), metadatafile=N'C:\Temp\TempDbGrowth3.xem') WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=1 SECONDS, MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=ON) GO