Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: