根据文件名从默认跟踪中提取自动增长事件

Question

我使用下面的查询获取sql服务器中数据库发生的自动增长事件。

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

它输出自动增长的数量、自动增长所需的时间和文件的逻辑名称.但是我想要这个文件的物理名称(mdf和ldf文件名)，而不是逻辑名称，我不知道我是从sys.traces获得物理名称，还是请用另一种方法来帮助我。

Answer 1

正如Remus在注释中提到的那样，您必须将跟踪表的结果加入到sys.master_files以获取物理文件名。

这里是一个完整的查询。请注意，跟踪中的持续时间是微秒，我已经相应地调整了计算，以输出更合理的度量(希望您没有实际的自动增长分钟)：

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);

Answer 2

我有另一个简单的解决办法。实际上很简单，我只需要添加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