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:

[sourcecode language=”sql”]

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[/sourcecode]

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.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

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

  1. 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. 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.

Comments are closed.