Here is a quick Extended Events script I knocked up where I wanted to track Tempdb file size changes for both the data and log file. I wanted to know who caused the tempdb growth, when it was done, what the T-SQL was and what sizes were involved. Not exactly complicated but hopefully useful.
CREATE EVENT SESSION [GetTempDB] ON SERVER ADD EVENT sqlserver.database_file_size_change( ACTION( package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name, sqlserver.is_system,sqlserver.nt_username,sqlserver.sql_text) WHERE database_id = 2 ) ADD TARGET package0.ring_buffer(SET max_memory=(102400)) GO ALTER EVENT SESSION [GetTempDB] ON SERVER STATE = START
I decided to use the ring buffer (with a max memory setting) I know the XEL file is much more widely used and usually preferred but I am kind of stuck in my ways a little! Anyways, I change the file sizes of Tempdb and then watched the live data.
It’s in the following format which is never pleasing on the eye.
I much prefer using this technique.
DECLARE @ShredMe XML; SELECT @ShredMe = CAST(target_data AS XML) FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address WHERE s.name = N'GetTempDB'; SELECT QP.value('(data[@name="database_id"]/value)', 'INT') as [DatabaseID], QP.value('(action[@name="nt_username"]/value)', 'varchar(50)') as [User], QP.value('(action[@name="client_app_name"]/value)', 'varchar(100)') as [App], QP.value('(action[@name="database_name"]/value)', 'varchar(50)') as [Database Name], QP.value('(action[@name="sql_text"]/value)', 'varchar(200)') as [TSQL], QP.value('(data[@name="file_type"]/text)', 'varchar(50)') as [File Growth Type], QP.value('(@timestamp)', 'datetime2') AS [timestamp of growth], QP.value('(data[@name="size_change_kb"]/value)', 'BIGINT') as [size_change_kb] FROM @ShredMe.nodes('RingBufferTarget/event[@name=''database_file_size_change'']') AS q(QP);
ALTER EVENT SESSION [GetTempDB] ON SERVER STATE = STOP GO DROP EVENT SESSION [GetTempDB] ON SERVER
I love the way yout present the data from the XE, how would you adjust your last query to pull the data from a local package file?
Hello. I actually do not have examples of querying from XEL files. But you can see how its done from this link, its quite similar (parsing through the xml etc.) https://blogs.msdn.microsoft.com/extended_events/2010/03/08/reading-event-data-101-whats-up-with-the-xml/