Pulling autogrowth events by file name from default trace

Question

I use below query to get auto-growth event occurred to databases in a sql server.

DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1
SELECT COUNT(*)as no_of_autogrowths,
sum(duration/(1000*60)) as time_in_min,
Filename
FROM ::fn_trace_gettable(@trcfilename, default)
WHERE (EventClass = 92 OR EventClass = 93)
GROUP BY  Filename

It outputs number of auto-growths, time taken for auto-growth and logical name of the file. But I want physical name of the file (mdf and ldf file name) instead of logical name.I don't know whether from sys.traces I will get physical name or please help me with an alternate way to do it.

score 6 · Answer 1 · answered Jul 17 '13 at 16:54

As Remus mentioned in a comment, you have to join the trace table result to sys.master_files to get the physical file names.

Here is a completed query. Note that the duration in the trace is in microseconds, and I have adjusted the computation accordingly to output a more sensible metric (hopefully you don't have actual minutes of auto-growth happening):

DECLARE @trcfilename nvarchar(2048);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1;

SELECT
    DB_NAME(mf.database_id) AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.physical_name AS PhysicalFileName,
    a.NumberOfGrowths,
    CAST(a.DurationOfGrowthsInSeconds AS decimal(18, 3)) AS DurationOfGrowthsInSeconds
    FROM
    (
        SELECT
            tt.DatabaseID AS database_id,
            tt.FileName AS LogicalFileName,
            COUNT(*) AS NumberOfGrowths,
            SUM(tt.Duration / (1000 * 1000.0)) AS DurationOfGrowthsInSeconds
            FROM sys.fn_trace_gettable(@trcfilename, default) tt
            WHERE (EventClass IN (92, 93))
            GROUP BY
                tt.DatabaseID,
                tt.FileName
    ) a
    INNER JOIN sys.master_files mf ON
        (mf.database_id = a.database_id) AND
        (mf.name = a.LogicalFileName);

score 2 · Answer 2 · answered Jul 18 '13 at 09:24

I got another easy solution. It was simple actually.I just needed to add DatabaseName.

DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1
SELECT COUNT(*)as no_of_autogrowths,
sum(duration/(1000*60)) as time_in_min,FileName,DatabaseName
FROM ::fn_trace_gettable(@trcfilename, default)
WHERE (EventClass = 92 OR EventClass = 93)
GROUP BY  Filename,DatabaseName

Pulling autogrowth events by file name from default trace

2 Answers2