"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: