This post contains a script to show database and log file growth events on a SQL Server instance. I often find a need to use this script to check what databases have been growing on unmonitored SQL Servers. You know, those SQL Servers that you only need to touch when something breaks. It’s not your fault there’s no room for proactivity, don’t worry.
When there’s a lot of growth events in the query result I tend to copy it out into Excel and filter it. You might want to save it to a table and sum the total growth per database… I’m only thinking about doing that now. I might come back to this and add a query for that!
If you want to know more about database growth events, see my other post here, where I try to explain what it all is with some other useful links.
Script to Show Growth Events
Run the following within any database scope.
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 DESC