Database File Auto-Growth History
Here’s a quick script to find the recent history of file auto growth on a MSSQL system. I work with a large OLTP database that’s partitioned monthly. If we get files auto-growing toward the end of a month when the partition is already upwards of 60GB, timeouts start to crop up. Sometimes finding out where those timeouts are coming from aren’t readily apparent. This script helped us out when we were searching for recent file growth.
SET NOCOUNT ON DECLARE @path VARCHAR(max), @cmd VARCHAR(4000); SELECT @path = CONVERT(varchar(max),value) FROM fn_trace_getinfo(DEFAULT) WHERE property = 2 SET @cmd = 'dir /b "' + @path + '"' -- PRINT @cmd DECLARE @files TABLE ( fn VARCHAR(64) ) INSERT @files EXEC master..xp_cmdshell @cmd DELETE @files WHERE fn IS NULL SELECT e.DatabaseName , e.[FileName] , e.SPID , e.Duration , e.StartTime , e.EndTime , CASE e.EventClass WHEN 92 THEN 'Data' WHEN 93 THEN 'Log' END AS 'FileType' , f.fn AS 'TraceFile' , te.name FROM @files f CROSS APPLY fn_trace_gettable(@path, DEFAULT) e JOIN sys.trace_events te ON e.EventClass = te.trace_event_id WHERE e.EventClass IN (92,93) ORDER BY e.StartTime DESC