Monday, October 30, 2017

How do you open a SQL Server Profiler trace file?

"Do you see anything in those traces? I am not sure I know how to read them properly, how do you open the file?"


A question from one of my customers today.  Last week they began having unexplained server disconnects early each morning at about 1:30AM.  I reviewed the system -- SQL Server and Windows logs, current and previous, as well as general state of the server and databases -- and I saw no problems.  I decided to setup a Profiler Trace to run during the time in question, hoping to capture the cause of the disconnects.

Well, I scheduled and ran the trace...  and now we need to read it.  :)

Very easily done using the fn_trace_gettable.  Here I've sampled a quick SELECT star and another more specific SELECT from the trace output.

       SELECT * FROM master..fn_trace_gettable(
        'C:\Users\aparahnevi\MForceTrace20171026.trc', DEFAULT);


SELECT
       TextData,
       StartTime,
       EndTime,
       CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds,
       DatabaseName,
       ApplicationName,
       SPID AS SessionId,
       ObjectName,
       Error,
       EventClass AS EventNumber,
       RowCounts,
       CPU AS CPUInMiliseconds,
       ServerName
FROM
       master..fn_trace_gettable(
           'C:\Users\aparahnevi\MForceTrace20171026.trc', DEFAULT)
WHERE
       TextData IS NOT NULL
       AND Duration IS NOT NULL;


That's it!  As I said, pretty quick and easy.  Here's a quick look at the output:

















I'll take some time later to post instructions for how I actually scheduled the trace.

Take a look at this for more details on fn_trace_gettable: