Autogrowth Analysis in PROD DB based on the trace file data

Question

I have used the below query to check our Production Database Autogrowth events so that we can set to a value which is much more realistic than the current value of 200 mb , unrestricted for mdf file and 10 % Restricted to 2048 gb for ldf file. The current version of our Database is SQL Server 2012 RTM Standard Version and we are managing it through AWS RDS, the total disk space is 2TB. When I have used the trace file query ,it shows me data only from 08052018 as you can see in the screenshot attached

Can anyone explain why am i seeing data only from 08052018 and not earlier to that?

Answer 1

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.