Sql-server – Pulling autogrowth events by file name from default trace

sql serversql-server-2008-r2trace

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.

Best Answer

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

Related Solutions

Sql-server – the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this

A tablespace creates a layer of abstraction between physical and logical data

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower disk.

Sql-server – Default trace enabled but not active

I would say there is a strong correlation between your out of space event and the missing trace. Note that the sp_configure option merely tells you that the default trace is enabled, but that does not mean that it is running or that it even exists. Note that sys.traces is not a table but a view:

create view sys.traces as select * from OpenRowset(TABLE SYSTRACES)

What does the TABLE SYSTRACES rowset provide? How does it work? How are its results filtered? Your guess is as good as mine. It is possible that the trace is still there, but in a state that prevents it from being exposed by this view. And it may be in a state that still prevents it from being started even after restarting the service.

First, make sure the location of the default trace has sufficient space, the SQL Server service account still has adequate permissions to write to it, you aren't subject to any space quotas, etc. You can get the location from the registry:

HKEY_LOCAL_MACHINE\Software\Microsoft\...YourInstance...\Setup\SQLDataRoot\

Once you are sure that SQL Server should be able to write to this folder, then you can disable and re-enable the default trace:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;

You shouldn't need to restart the SQL Server service at this point, but may be a final kick in SQL Server's pants if you still don't see a row in sys.traces. Note that the trace_id you get is not guaranteed to stay at 1.

Best Answer

Related Solutions

Sql-server – the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this

Sql-server – Default trace enabled but not active

Related Question