Sql-server – how to find out what process(es) caused the autogrowth of a database or a database file

processsql serversql server 2014tempdb

I have a process(es) hungry for tempdb, but I am struggling to identify this process.

any ways I could achieve this?

We have been alerted as Tempdb has now grown into the space you
reclaimed on the T:\ drive earlier. Again, there is 10MB remaining on
disk. I can see a number of auto growth events across the data files
on the REP instance starting at 10:18AM this morning. In total there
were 330 auto growth events at 512MB each, totalling 168GB.

It is difficult to highlight after the fact what has used this space
in Tempdb, are you aware of any processes that may be using Tempdb in
this manner today?

/————————————————————\

Identifying How Often an Auto-growth Event has Occurred

When SQL Server performs an auto-grow event, the transaction that triggered the auto-grow event will have to wait until the auto-grow event completes before the transaction can finish. These auto-growth events cause your performance to degrade a little when an auto-grow event is taking place. For this reason it is best if you can size your database appropriately so auto-growth events rarely occur.

If you are interested in how often an auto-growth event occurs on your system you can capture those events using a trace. By knowing which databases are performing auto-growth events allows you to adjust those database file growth properties so they will perform auto-growth events less frequently. You can use the profiler “Data File Auto-grow” and/or the “Log File Auto-grow” events to track these database auto-growth events. If you are running SQL Server 2005 or above, both these auto-grow events are already being captured by the default trace. If you haven’t turned off the default trace then you can use the default trace file to find these auto-grow events. If you have turned off the default trace you can either enable it, or setup a new profiler trace to capture the “Data File Auto-grow” and “Log File Auto-grow” events.

The default trace logs to a file. I have provided the code in Listing 4 to show you how to extract all the auto-growth events from the default trace files. If you create your own profiler trace session to capture these auto-grow events then you will need to modify this script to meet your profiler trace settings.

https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

marcelo miorelli
11-mar-2014

\*--------------------------------------------------*/
DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT 
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB 
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg 
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

Best Answer

The only way to find out what process caused the autogrowth is to use Extended events esp. EVENT --> sqlserver.database_file_size_change & sqlserver.databases_log_file_size_changed and ACTION --> sqlserver.sql_text.

Looks like @DBA_ANDY already did the hard work of writing an XEvent

-- Original Author : @DBA_ANDY http://nebraskasql.blogspot.com/2016/06/finding-file-growths-with-extended.html
-- Modified by : Kin to filter on tempdb autogrowth events
SET NOCOUNT ON 
/* Create Extended Events Session */
IF EXISTS (SELECT 1 FROM master.sys.server_event_sessions WHERE name = 'DemoFileSize')
 DROP EVENT SESSION [DemoFileSize] ON SERVER
GO
CREATE EVENT SESSION [DemoFileSize] ON SERVER
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
    ACTION(package0.collect_system_time,sqlos.task_time,
 sqlserver.client_app_name,sqlserver.client_hostname,
 sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
 sqlserver.server_instance_name,sqlserver.session_id,
 sqlserver.sql_text,sqlserver.username)
 WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
),
 /* Note -  predicate/filter - will collect only for tempdb */
ADD EVENT sqlserver.databases_log_file_size_changed(
    ACTION(package0.collect_system_time,sqlos.task_time,
 sqlserver.client_app_name,sqlserver.client_hostname,
 sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
 sqlserver.server_instance_name,sqlserver.session_id,
 sqlserver.sql_text,sqlserver.username)
 WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
  )
 /* Note -  predicate/filter - will collect only for tempdb */
ADD TARGET package0.event_file(SET filename=N'D:\XEvent_logs\DemoFileSize.xel',-- change HERE !!
 max_file_size=(500),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

ALTER EVENT SESSION [DemoFileSize] ON SERVER
STATE = START;
GO

shred the XEvent xml ...

SELECT
Case when file_type = 'Data file' Then 'Data File Grow' Else File_Type End AS [Event Name]
, database_name AS DatabaseName
, dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), timestamp1) as LocalTimeStamp
/* added the timestamp and in XE is UTC - this code converts it to local server time zone */
, file_names
, size_change_mb
, duration
, client_app_name AS Client_Application
, client_hostname
, session_id AS SessionID
, sql_txt   ----------------> This is the process TSQL text !
, sql_username
, Is_Automatic

FROM
(
SELECT
(n.value ('(data[@name="size_change_kb"]/value)[1]', 'int')/1024.0) AS size_change_mb
, n.value('(@timestamp)[1]', 'datetime2') as timestamp1
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS Client_HostName
, n.value ('(data[@name="file_name"]/value)[1]','nvarchar(50)') AS file_names
, n.value ('(data[@name="is_automatic"]/value)[1]','nvarchar(50)') AS Is_Automatic
, n.value ('(action[@name="sql_text"]/value)[1]','nvarchar(500)') AS sql_txt
, n.value ('(action[@name="username"]/value)[1]','nvarchar(50)') AS sql_username

FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'D:\XEvent_logs\DemoFileSize*.xel' -- CHANGE HERE !!
, NULL
, NULL
, NULL
)
) AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
ORDER BY timestamp1 desc

Below is the output:

enter image description here Important things :

Related Question