Advertisements
Home > Administration > Getting Database Data/Log File Growth from Default Trace

Getting Database Data/Log File Growth from Default Trace

Since SQL Server 2005 we have had default trace; we can mine it to see how often the databases are growing and by how much.  You might not get any information nature of the default trace.  However, if information is there you can use following script to get growth information.

-- SQLCanada, Mohit K. Gupta
-- Script from https://sqlcan.wordpress.com
-- Last Updated: May 12, 2012
------------------------------------------   

-- Get Default Trace File name

DECLARE @TraceFileName NVARCHAR(512)  
SELECT @TraceFileName = path
  FROM sys.traces
 WHERE id = 1

-- Database Growth by Database by Date
  SELECT DB_Name(DatabaseID) AS DatabaseName,
         CASE WHEN EventClass = 92 THEN
            'Data File'
         ELSE
            'Log File'
         END AS FileType,
         Convert(varchar(8),StartTime,112) AS GrowthDate,
         Sum((IntegerData*8)/1024) AS GrowthInMB
    FROM sys.fn_trace_gettable(@TraceFileName,default)
   WHERE (EventClass = 92) OR (EventClass = 93)
GROUP BY DB_Name(DatabaseID), EventClass, Convert(varchar(8),StartTime,112)
ORDER BY DatabaseName,
         GrowthDate

-- Total Growth by Date Only
  SELECT CASE WHEN EventClass = 92 THEN
            'Data File'
         ELSE
            'Log File'
         END AS FileType,
         Convert(varchar(8),StartTime,112) AS GrowthDate,
         Sum((IntegerData*8)/1024) AS GrowthInMB
    FROM sys.fn_trace_gettable(@TraceFileName,default)
   WHERE (EventClass = 92) OR (EventClass = 93)
GROUP BY EventClass, Convert(varchar(8),StartTime,112)
ORDER BY GrowthDate

Example output:

Example Output

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: