SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Tracking Tempdb Size

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.

XMLFORM

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); 

TEMPTRACKING

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

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...