Sql-server – Autogrowth Analysis in PROD DB based on the trace file data

You are most likely looking into somethng known as default trace, a light weight trace that runs in the background when SQL Server starts. To see if default trace is enabled:

SELECT value_in_use 
    FROM sys.configurations 
    WHERE configuration_id = 1568 /* 1 indicates the trace is enabled */

To view the location of trace files:

SELECT  REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 256)) AS DefaultTraceLocation
FROM    sys.traces
WHERE   is_default = 1

You will find four trace files here, and each files are about 5 mb in size. When all four files are full, it rolls over (removes the oldest file from the disk) and a new trace file is created. Therefore, you might not be able to keep track of older data unless you siphon it off to a customized table somewhere.

You can view auto growth setting for your current trace file using:

/*Author - Feodor Georgiev*/
SELECT  TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Data File Auto Grow'
ORDER BY t.StartTime

You can also pull the data from previous trace files by slightly modifying the previous query.

SELECT  TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc' 
                                                        FROM sys.traces
                                                            WHERE path LIKE '%\MSSQL\Log\log%.trc'
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Data File Auto Grow'
ORDER BY t.StartTime 

An example on how to load this data into a table

CREATE TABLE [dbo].[DefaultTrace_History](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [instancename] [nvarchar](256) NOT NULL,
    [EventName] [nvarchar](128) NOT NULL,
    [DatabaseName] [nvarchar](256) NOT NULL,
    [DatabaseID] [int] NOT NULL,
    [NTDomainName] [nvarchar](256) NULL,
    [ApplicationName] [nvarchar](256) NOT NULL,
    [LoginName] [nvarchar](256) NOT NULL,
    [SPID] [int] NOT NULL,
    [Duration] [int] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [GrowthInMB] [decimal](5,2) NOT NULL,
    [FileName] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO

Procedure to load the data incrementally:

CREATE OR ALTER PROCEDURE [dbo].[SP_Load_DefaultTrace_History] 
AS 

    DECLARE @maxdt DATETIME 
    DECLARE @sql NVARCHAR(max) 

    SELECT @maxdt = max(starttime) 
    FROM   [DefaultTrace_History] 

    SET @sql= 
    N'INSERT INTO [DefaultTrace_History] (InstanceName,EventName,DatabaseName,DatabaseID,NTDomainName,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,GrowthInMB,FileName)
    SELECT  @@servername,
        TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime,
        convert(decimal(19,2),t.IntegerData*8/1024) AS GrowthInMB,
        smf.physical_name AS [FileName]
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX(''\'',REVERSE(path)),LEN(path)))+''log.trc'' 
                                                        FROM sys.traces
                                                            WHERE path LIKE ''%\MSSQL\Log\log%.trc''
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.master_files smf ON smf.database_id = t.DatabaseID
WHERE   te.name = ''Data File Auto Grow'''
    IF (SELECT count(1) 
        FROM  [DefaultTrace_History] 
        WHERE  instancename = @@servername) = 0 
      BEGIN 
          EXEC sp_executesql @sql 
      END 
    ELSE 
      BEGIN 
          SET @sql= @sql + ' AND @@servername = @@servername AND t.StartTime >' + quotename(convert(VARCHAR(25), @maxdt, 121),'''') + ' ORDER BY t.StartTime DESC' 
          EXEC sp_executesql @sql 
      END 

--Purge records > 365 days
DELETE FROM [DefaultTrace_History] WHERE [instancename] = @@servername AND [StartTime] < dateadd(dd,-365,getdate())
GO

You can then create a job to run on a schedule to load the data.