SQL Server 2005 - The Default Trace

xmldeveloper — Mon, 08 Oct 2007 16:33:36 GMT

What's in a name? That which we call this trace by any other word would mean it’s stopped? But nay, it’s running quietly on your server and I will tell you why.

The Default Trace is a light-weight trace that is running by the default on your SQL Server. Its primary role is to help you in troubleshooting various problems. Light-weight means that you shouldn't be too concerned about its impact on your server’s performance.

Let’s get some more information about it. Using the fn_trace_getinfo function, you can get some of its properties. The Default Trace has the id value 1 so I will call the function fn_trace_getinfo using 1 as argument.

Example:
SELECT *
FROM fn_trace_getinfo(1)
GO

--Results
traceid property value
------- -------- ----------------------------------------
1       1        2
1       2        C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_156.trc
1       3        20
1       4        NULL
1       5        1

I will explain the results, don’t worry! The property column has the following meaning: 1= Trace options, 2 = File name, 3 = Max size, 4 = Stop time, 5 = Current trace status. In our case, we see a rollover trace having a maximum size of 20MB. Rollover means that when the maximum size of the file is reached a new file will be created. The number appended to the name of the trace (156 in this case) is used to indicate its sequence. There are only 5 trace files just in case you wonder if it will fill-up your disk drive’s space. The trace file location can be different on your server (and you can see the exact location using the above query).

The next step is to see the events recorded by this trace:

Example:
SELECT T.eventid AS [id], E.name
FROM fn_trace_geteventinfo(1) T
JOIN sys.trace_events E
ON T.eventid = E.trace_event_id
GROUP BY T.eventid, E.name
GO

-- Results
id    name
----- -----------------------------
18    Audit Server Starts And Stops
20    Audit Login Failed
22    ErrorLog
46    Object:Created
47    Object:Deleted
55    Hash Warning
69    Sort Warnings
79    Missing Column Statistics
80    Missing Join Predicate
81    Server Memory Change
92    Data File Auto Grow
93    Log File Auto Grow
94    Data File Auto Shrink
95    Log File Auto Shrink
102   Audit Database Scope GDR Event
103   Audit Schema Object GDR Event
104   Audit Addlogin Event
105   Audit Login GDR Event
106   Audit Login Change Property Event
108   Audit Add Login to Server Role Event
109   Audit Add DB User Event
110   Audit Add Member to DB Role Event
111   Audit Add Role Event
115   Audit Backup/Restore Event
116   Audit DBCC Event
152   Audit Change Database Owner
153   Audit Schema Object Take Ownership Event
155   FT:Crawl Started
156   FT:Crawl Stopped
157   FT:Crawl Aborted
164   Object:Altered
167   Database Mirroring State Change

The names of the events are self-explanatory. You can see that memory changes will be recorded as well as DBCC commands execution, DDL events (creating or dropping objects, schema modifications) etc.

But where you can see the recorded information? You will find the answer to this question looking at the Summary Reports in SQL Server Management Studio. To view these reports, start SQL Server Management Studio, press F7 (or click Summary in the View menu), and finally use the Reports dropdown list to select a report.

As you may expect, you can get the same data by running a SQL query:

Example:
SELECT *
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)
GO

I won’t include the results because they are pretty large and they don't look as nice as the graphical reports in Management Studio. I will recommend you to try the scripts above and to test-drive the new Summary Reports, I can tell you that it’s worth it. They can give you a good idea of what happens or happened on your server from object creation to configuration changes. In the end, the Default Trace along with the Summary Reports and the new Dynamic Management Views will make your life easier (provided you work with SQL Server of course).

[published in Culminis Compass http://emea.culminis.com/Newsletters/200603_march/0306defaulttrace.htm]

How-To Articles

SQL Server 2005 - The Default Trace