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)[1]', 'INT') as [DatabaseID],
QP.value('(action[@name="nt_username"]/value)[1]', 'varchar(50)') as [User],
QP.value('(action[@name="client_app_name"]/value)[1]', 'varchar(100)') as [App],
QP.value('(action[@name="database_name"]/value)[1]', 'varchar(50)') as [Database Name],
QP.value('(action[@name="sql_text"]/value)[1]', 'varchar(200)') as [TSQL],
QP.value('(data[@name="file_type"]/text)[1]', 'varchar(50)') as [File Growth Type],
QP.value('(@timestamp)[1]', 'datetime2') AS [timestamp of growth],
QP.value('(data[@name="size_change_kb"]/value)[1]', 'BIGINT') as [size_change_kb]
FROM @ShredMe.nodes('RingBufferTarget/event[@name=''database_file_size_change'']') AS q(QP); 

Clean-up.

ALTER EVENT SESSION [GetTempDB]
ON SERVER   STATE = STOP
GO
       DROP EVENT SESSION [GetTempDB] ON SERVER

Enjoy.

Filed under: Admin, Extended Events, SQL SERVER Tagged: Admin, Extended Events, Microsoft, SQL server, Tempdb, TSQL