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.
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
