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