1

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
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.