Read SQL Server Profiler Trace files with SELECT statement

ReadRead SQL Server Profiler Trace files with SELECT statement

Earlier I have written article about how to automate process to start and stop SQL Server Profiler Trace. Server side tracing generates .TRC file which is hard to read if you do it manually.

I like set based data and it is great if we can execute SELECT statement on trace file so that either we can see properly managed result set or store the result in SQL Server table by INSERT….SELECT command or by SELECT….INTO command.

As I said trace files are very powerful weapon of DBA and they can’t afford to make distance of it. Trace file can keep close eyes on each batch / query / SP runs in your database for 24*7 and helps you to debug the issues. Trace file always helps me a lot in finding the performance related issues.

I will use the same trace file of AdventureWorks2012 database which I have generated from my earlier article. You can generate same kind of file from the article or you can have your own trace file.

Here is the SELECT statement I use:


SELECT

ServerName

,DatabaseName

,SPID AS SessionId

,ObjectName

,EventClass AS EventNumber

,RowCounts

,CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds

,CPU AS CPUInMiliseconds

,StartTime

,EndTime

,TextData

FROM fn_trace_gettable('D:\TraceCollection\AdventureWorks2012.trc', DEFAULT)

GO

This script is very easy yet powerful and part of my day-to-day DBA duty.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

5 thoughts on “Read SQL Server Profiler Trace files with SELECT statement

  1. kalu

    Hi RiteshBhai.

    I have one Trace file (smxp.trc) and it is open in SQL Server Profiler but it not run in this Script . also i have set path
    proper and i try it using all system database like msdb,master and model.

    Error like this.

    Msg 567, Level 16, State 1, Line 1
    File ‘D:\smxp_1.trc’ either does not exist or is not a recognizable trace file. Or there was an error opening the file.

    1. Riteshshah Post author

      Kalu, If you ran exact same query I provided in article along with proper file name then there is only one possibility that you are not having sufficient permission to read folder where .TRC file located.

  2. Pingback: Milestone of 500+ article

Comments are closed.