Systems Engineering and RDBMS

Usefulness of the Default Trace in SQL Server 2005

Posted by decipherinfosys on December 6, 2008

We had covered black box trace and the default trace before in our post here. In response to that post, one of the reader made a good point about the usefulness of the default trace which we would like to share with everyone here.

The default trace on a development test system is getting logged to this folder:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\

And the most recent one over there is this file: log_100.trc. You can locate your trace file on the system and then replace it in the code shown below.

Let’s quickly take a look at the top 10 records in this trace sorted by the starttime in a descending order. And let’s also join it with sys.trace_events so that we can filter the data based on the trace event names:

SELECT top 10
trc_evnt.name
,dflt_trc.DatabaseName
,dflt_trc.ApplicationName
,dflt_trc.TextData
,dflt_trc.FileName
,dflt_trc.LoginName
,dflt_trc.StartTime
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_100.trc', NULL) AS dflt_trc
INNER JOIN sys.trace_events AS trc_evnt
ON dflt_trc.EventClass = trc_evnt.trace_event_id
ORDER BY dflt_trc.StartTime DESC

And now, let’s look at what specific events were caught in this trace file:

SELECT
trc_evnt.name
,count(*) as occurence
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_100.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
order by trc_evnt.name

On the system, this was the output:

Name                                     occurence
---------------------------------------- -----------
Audit Add DB User Event                  80
Audit Add Member to DB Role Event        80
Audit Backup/Restore Event               1492
Audit Change Audit Event                 4
Audit Change Database Owner              41
Audit DBCC Event                         156887
Audit Login Failed                       46
Audit Schema Object GDR Event            3369
Audit Server Alter Trace Event           187
Data File Auto Grow                      15
ErrorLog                                 1
Hash Warning                             1
Log File Auto Grow                       111
Log File Auto Shrink                     234
Missing Column Statistics                64
Missing Join Predicate                   11
Object:Altered                           224
Object:Created                           18316
Object:Deleted                           4098
Server Memory Change                     1

And now, we can narrow it down and look for specifics like for example – one of the common things that a DBA might be interested in would be the auto growth – in a production system auto growth is not something that you should allow – you should size the files properly so as to not incur the cost of the auto growth. And by querying this output, you can get much more detailed information on when it happened and on which databases:

SELECT
trc_evnt.name
,dflt_trc.DatabaseName
,dflt_trc.ApplicationName
,dflt_trc.TextData
,dflt_trc.FileName
,dflt_trc.LoginName
,dflt_trc.StartTime
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_100.trc', NULL) AS dflt_trc
INNER JOIN sys.trace_events AS trc_evnt
ON dflt_trc.EventClass = trc_evnt.trace_event_id
WHERE trc_evnt.name like '%Auto Grow%'
ORDER BY dflt_trc.StartTime DESC

And replace the search value with Auto Shrink since that is another thing that should not be happening in the system. Likewise, you can look for other pieces of information as well. So, there are a lot of very valuable usages of the default trace and one should look into adding such SQLs into their monitoring SQL code.

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: