August 12, 2013 at 5:25 am
Hi Experts,
How can i prove whether the file growth happened on particular time ? We had space crunch in tempdb and i killed a transaction which released enough space but is there any way that i can show that the log growth happened on this particular time?
Thanks IN Advance.
August 12, 2013 at 5:57 am
This query will give you the file growth time, size and duration
Customize it as per your need and you have to have the trace file available on server for that , so run this on a particular interval and update it on a table and refer it later.
use traceid = 2 in case audit trace is running
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 'Duration(MS)'
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
Regards
Durai Nagarajan
August 12, 2013 at 6:19 am
Thanks Durai
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply