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.
