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]
Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro