Using the Default Trace

In SQL Server 2005 a new trace was introduced.  This trace is enabled by default and has to be explicitly disabled, so chances are it is enabled in your environment. This trace is pretty lightweight, so it does not have a huge impact on your server.  The benefit of having it outweighs the minute cost of running it, in my mind. I will be very general about the trace, for this post, because I have already posted an in depth guide for SQL Server Central, http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/.  

The first thing we need to determine is the status of the trace.  Like I said before the trace is enabled by default, but we can check by issuing the following query:

SELECT * FROM sys.configurations WHERE configuration_id = 1568

So what information does this trace contain? Well the answer is listed below:

Database

• Data file auto grow
• Data file auto shrink
• Database mirroring status change
• Log file auto grow
• Log file auto shrink

Errors and Warnings

• Errorlog
• Hash warning
• Missing Column Statistics
• Missing Join Predicate
• Sort Warning

Full-Text

• FT Crawl Aborted
• FT Crawl Started
• FT Crawl Stopped

Objects

• Object Altered
• Object Created
• Object Deleted

Security Audit

• Audit Add DB user event
• Audit Add login to server role event
• Audit Add Member to DB role event
• Audit Add Role event
• Audit Add login event
• Audit Backup/Restore event
• Audit Change Database owner
• Audit DBCC event
• Audit Database Scope GDR event (Grant, Deny, Revoke)
• Audit Login Change Property event
• Audit Login Failed
• Audit Login GDR event
• Audit Schema Object GDR event
• Audit Schema Object Take Ownership
• Audit Server Starts and Stops

Server

• Server Memory Change

As you can see there are a lot of useful events in the trace that will make your life easier. Using this trace file is as easy as loading any trace file into SSMS.  You only need to use the system function ::fn_trace_gettable(). Once the trace file is loaded you can filter or specify in columns you want, like a normal query.

The first step you should do when deciding to query the default trace is find the trace file location. You can use the function ::fn_trace_getinfo(0) to obtain the trace path.

--get the current trace rollover file
SELECT * FROM ::fn_trace_getinfo(0)

A great example of where the default trace shines is when you have a process or rogue developer that is dropping objects, in your database.  Under normal circumstances you will not have Profiler running and may not have DDL triggers in place.  So how can you tell, who is dropping objects and when? The answer lies in the default trace. Below is a sample of how to obtain dropped objects.

Note: Make sure you change your trace file to your file location.

SELECT 
     loginname,
     loginsid,
     spid,
     hostname,
     applicationname,
     servername,
     databasename,
     objectName,
     e.category_id,
     cat.name as [CategoryName],
     textdata,
     starttime,
     eventclass,
     eventsubclass,--0=begin,1=commit
     e.name as EventName
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
     INNER JOIN sys.trace_events e
          ON eventclass = trace_event_id
     INNER JOIN sys.trace_categories AS cat
          ON e.category_id = cat.category_id
WHERE databasename = 'TraceDB' AND
      objectname IS NULL AND --filter by objectname
      e.category_id = 5 AND --category 5 is objects
      e.trace_event_id = 47 
      --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter Obj

Now you have the ability to answer the key questions your boss is probably asking you; such as who dropped the object and when was it dropped.

For a complete tutorial on how to enable and use the trace. Please follow the following link: http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/.  You will have to register to read the article, but registration is free and gives you access to a lot of great resources.