Menu & Search
Get Database Growth Events in SQL Server

Get Database Growth Events in SQL Server


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

Share

0 Comments