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:

path

————————————————————————-

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 
   e.name 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