Quick one today:
Once in a while we need a way to quick check how the database files are growing. This below script allows us to query the last few “AutoGrowth” events on database files.
Every time, autogrowth happens, it is logged in the trace file. Since Sql Server has a default trace file running all the time, we query it for autogrowth events.
Note: Data in trace file only stays there for a short period of time (depending on retention), so if this particular event did not occur in the recent past it may not be in the trace file anymore.
According to Event Class List:
- 92 is for Data file auto growth
- 93 is for Log file auto growth
-- -- Get the trace file name -- DECLARE @TraceFile NVARCHAR(1000); SELECT @TraceFile = path FROM sys.traces WHERE is_default = 1; -- -- Look for 'AutoGrowth' events in TraceFile -- SELECT TOP 10 DatabaseID AS [Database_ID] , DB_NAME(DatabaseID) AS [Database_Name] , FileName AS [DatabaseFileName_Logical] , (Duration*1.0/1000000) AS [TimeSpentOn_AutoGrowth] FROM sys.fn_trace_gettable(@TraceFile, DEFAULT) WHERE EventClass IN ( 92 -- DataFile Growth , 93) -- LogFile Growth AND DatabaseID = DB_ID() GO