Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

The Voice of the DBA

Steve Jones is the editor of and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at

Finding the Default Trace File

A post more for me than for anyone else, since I’ll look for something in the default trace and I often need this snippet of code:

select path 
 from sys.traces 
 where is_default = 1

That returns something like this:



C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_92.trc

This is the current file being used by the trace, which allows me to then look into the file for some event.

From here, I usually start running a query like this:

select as eventclass
 , t.textdata
 , t.starttime
 , t.error 
 , t.hostname
 , t.ntusername
 , t.ntdomainname
 , t.clientprocessid
 , t.applicationname
 , t.loginname
 , t.spid
 from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_92.trc', default) t
  inner join sys.trace_events e 
     on t.eventclass = e.trace_event_id 
  where eventclass = xx

In this query, I take the output from the first query and use that as the FROM file and then include an event class number in the WHERE clause. I needed this today, running a check for the latest DBCC, and so I used the class 116.

You can get a list of event classes here: Trace Event Classes

Filed under: Blog Tagged: administration, sql server, syndicated


Leave a comment on the original post [, opens in a new window]

Loading comments...