--------------------------------------------------------------------------------
--  File        :   Demo 05_Select auto-growth events from the Default Trace,2005+.sql
--
--  Purpose     :   This script returns the events recorded by the default 
--                  trace for auto-growth operations on the files of databases 
--                  attached to the instance.
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   1.0004.0090.0000 (2010-02-02)
--------------------------------------------------------------------------------

USE [tempdb];

GO

SET NOCOUNT ON;

--  The path to the base Default Trace output file.  Each time that the trace 
--  rolls-over or is restarted, a new suffix is appended.  The 
--  [sys].[fn_trace_gettable] system function can target a specific trace file 
--  or a series.
DECLARE @BaseDefaultTraceFilePath NVarChar(260);

--  The path to the currently active output file of the Default Trace.
DECLARE @CurrentDefaultTraceFilePath NVarChar(260);

--  Look up the name of the currently active Default Trace file.
SELECT @CurrentDefaultTraceFilePath = [path]
FROM [sys].[traces]
WHERE ([path] LIKE N'%\log[_][0-9]%.trc');

--  Remove the suffix that indicates how many times the Default Trace has 
--  rolled-over/restarted.
SET @BaseDefaultTraceFilePath = SubString(
        @CurrentDefaultTraceFilePath,
        1,
        PatIndex(
            N'%\log[_][0-9]%.trc',
            @CurrentDefaultTraceFilePath
        )
    ) + N'log.trc';

--  Aggregate information on the auto-growth events.
WITH [CTE_GrowthEvents]
AS (
    SELECT Row_Number() OVER (
            ORDER BY [StartTime] ASC,
                [EventSequence] ASC
        ) AS [RowNumber1],
        Row_Number() OVER (
            PARTITION BY [SPID],
                [EventClass],
                [ApplicationName]
            ORDER BY [StartTime] ASC,
                [EventSequence] ASC
        ) AS [RowNumber2],
        Rank() OVER (
            ORDER BY [SPID],
                [EventClass],
                [ApplicationName]
        ) AS [GroupID],
        [SPID] AS [SessionID],
        [DatabaseName],
        [EventClass],
        [StartTime],
        [EndTime],
        [IntegerData],
        [ApplicationName],
        [LoginName]
    FROM [sys].[fn_trace_gettable] (
        @BaseDefaultTraceFilePath,
        DEFAULT
    ) AS [_Events]
    WHERE ([EventClass] IN (92, 93))
)
SELECT Min([StartTime]) AS [FirstGrowthOperationTime],
    Max([EndTime]) AS [LastGrowthOperationTime],
    Count([EventClass]) AS [GrowthOperationCount],
    [SessionID],
    [DatabaseName],
    (
        Case [EventClass]
            WHEN 92 THEN N'Data file'
            WHEN 93 THEN N'Transaction log file'
            ELSE NULL
        END
    ) AS [FileType],
    (Sum(Cast([IntegerData] AS BigInt)) * 8192) AS [CumulativeGrowth_bytes],
    [ApplicationName],
    [LoginName]
FROM [CTE_GrowthEvents]
GROUP BY [GroupID],
    ([RowNumber1] - [RowNumber2]),
    [DatabaseName],
    [SessionID],
    [EventClass],
    [ApplicationName],
    [LoginName]
ORDER BY [FirstGrowthOperationTime] ASC,
    [LastGrowthOperationTime] DESC,
    [CumulativeGrowth_bytes] DESC,
    [GrowthOperationCount] DESC,
    [SessionID] ASC;

GO
