Troubleshooting using default trace information
Posted by rahmanagoro on March 11, 2011
The default trace is built into SQL server, its one that often tells you operations that are happening on the database server which is often not provided within the SQL error logs.
I regularly query the default trace to pick up issues that might be arising on the database server, the scripts that I use to query the default trace can be found below. The default trace also allows me to create policies using policy based management within SQL 2008, we once had a deployment using Visual studio and for those who have used visual studio, it creatred databases as a project and this is used in storing database schema, database deployment, integration with SVN etc. At the end of the day, the repository was not upto date and Auto skrink was turned on with visual studio, I found this had been turned on with visual studio. This was revealed within the default trace and I was able to create a policy to check for this across the database estate.
To get the default trace file currently in use, run the query below.
SELECT * FROM fn_trace_getinfo(default);
To group the various events to see what has been happening on the database server, you can run the query below.
--Auditing various event types in the trace file. SELECT trc_evnt.name, trc_evnt.category_id ,count(*) as occurence FROM fn_trace_gettable('y:\databases\log_5170.trc', NULL) AS dflt_trc INNER JOIN sys.trace_events AS trc_evnt ON dflt_trc.EventClass = trc_evnt.trace_event_id group by trc_evnt.name, trc_evnt.category_id order by trc_evnt.name
You can change the default trace file to query the last five default traces, so for example y:\databases\log_5170.trc is the current trace, to check the one before this simply decrement the number by one, so run the query y:\databases\log_5169.trc.
I get information similar to the one below
Audit DBCC Event 8 9006
Audit Server Alter Trace Event 8 2
Hash Warning 3 2
Object:Created 5 644
Object:Deleted 5 201
Sort Warnings 3 1
I have used this method to troubleshoot various issues on the database such as the following:
missing statistics: I subesequently created and index or statistics on the column.
transaction logs growing every night: I changed the growth settings and expanded the size so as to remove the need for the growth.