Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
You need to join sys.master_files or sys.database_files on the machine that produced the trace. –  Remus Rusanu Jul 17 '13 at 12:58
add comment

2 Answers

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);
share|improve this answer
    
It works.But see my answer also.It was simple,but i didn't notice it. –  IT researcher Jul 18 '13 at 9:25
add comment

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
share|improve this answer
2  
Strictly speakign thsi is the answer to a different question, as you asked 'i want physical name of the file(mdf and ldf file name) instead of logical name'. –  Remus Rusanu Jul 18 '13 at 11:08
    
@RemusRusanu I agree with you.But actually i had problem with my query in my question where it returns only logical name.In some cases database name and logical name where not same(case in which db name changed after creating). So the output of query was confusing that which db had autogrowth.So i wanted to know database name.So i asked for physical name which can be identified easily.But when i get db name it is easy too. –  IT researcher Jul 18 '13 at 11:15
    
This isn't an answer to the question as asked, and the computation of the autogrowth duration is still incorrect. –  Jon Seigel Jul 18 '13 at 12:48
    
@JonSeigel Thank you for informing it.can you please help me with what is wrong with autogrowth duration? I got the query from some website.So i was using it from last two weeks. –  IT researcher Jul 18 '13 at 13:39
    
See my answer for an explanation. The duration from the trace is in microseconds, not milliseconds. –  Jon Seigel Jul 18 '13 at 15:54
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.