We have all been subject to or know someone who has been in a situation where an object has been altered/created/deleted, without our knowledge, and the application comes to a screeching halt. After fixing the problem, your boss asks you some questions, like what happened, why did it happen, and who did it. SQL Server 2005 introduced a new type of trigger called a DDL trigger that can provide all the answers we need; however, you did not get a chance to implement this functionality. So… what do you do?

Some would tell their boss “I do not know, but I can find out” and then search frantically for 3rd party tools to read the transaction log, hoping for instantaneous salvation. What these few do not know is an answer is silently running in the background. SQL Server 2005 has built-in functionality that gives administrators the answers to all these questions. The answers lie in a new background trace called the default trace.

The default trace is exactly what the name specifies, a trace. Default trace is always running in the background of your instance capturing events that administrators can use to troubleshoot problems. The default trace is enabled by default and does not burden the system because it is fairly lightweight. Chances are you had not even noticed this trace running on your instance. To those concerned about overhead, yes there is overhead, but in my mind the benefits far outweigh the minimal overhead.

The default trace is not intended to replace DDL trigger functionality and should be used as a means to monitor an SQL Instance, or quickly obtain detailed information about problematic events. The default trace does not capture all trace events, but captures enough information to become a powerful tool in your toolkit. The default trace captures key information including auditing events, database events, error events, full text events, object creation, object deletion and object alteration.

From my experiences and observations on forums, I will be focusing on object level events. It seems that a greater number of people want the “who done it” answer for object DDL events.

Let’s start by checking if the default trace is enabled:

SELECT * FROM sys.configurations WHERE configuration_id = 1568

If this feature is not available, you will have to configure the advanced option “default trace enabled”. Below is the code to enable the trace:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

Now that we have enabled the default trace, we need to find the trace file path:

SELECT * FROM ::fn_trace_getinfo(0)

Now that we have all the information we need, we can start exploring the trace data. Let’s create a new database called TraceDB:

USE [master]
GO
CREATE DATABASE TraceDB

Now, let’s open the trace file and see what information we can gather:

SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName, e.category_id, cat.name as [CategoryName], textdata, starttime, eventclass, eventsubclass, 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 e.category_id = 5 
AND e.trace_event_id = 46

As you can see, we were able to gather some pretty significant information about who created the database and when the database was created. We used category id of 5 and a trace_event_id of 46 to filter the data correctly. Event ID 46 represents Object:Created and category 5 is objects.

We can also query the default trace for specific objects. Let’s create a table called “MyTable” and see what information we can gather:

USE [TraceDB]
GO
CREATE TABLE [dbo].[MyTable]( 
[id] [int] IDENTITY(1,1) NOT NULL, 
[sometext] [char](3) NULL ) ON [PRIMARY]

Now, let’s query the default trace for the creation of “MyTable”:

SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName, e.category_id, cat.name, textdata, starttime, eventclass, eventsubclass, 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 = 'MyTable' 
AND e.category_id = 5 
AND e.trace_event_id = 46

Now, let’s take it a step further and alter “MyTable” and see what information we can gather:

USE [TraceDB]
GO
ALTER TABLE MyTable ADD col INT

We can now search trace information on the alter event for “MyTable”. We can use the same query as before but need to make a small modification. You must change the trace_event_id to 164 because event 164 represents the object:Altered event:

SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName, e.category_id, cat.name, textdata, starttime, eventclass, eventsubclass, 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 = 'MyTable' 
AND e.category_id = 5 
AND e.trace_event_id = 164

Finally, let’s drop “MyTable” and view the trace details:

USE [TraceDB]
GO
DROP TABLE MyTable

We can view trace data by changing the trace_event_id to 47, which represents the object:Deleted event:

SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName, e.category_id, cat.name, textdata, starttime, eventclass, eventsubclass, 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 = 'MyTable' 
AND e.category_id = 5 
AND e.trace_event_id = 47

As you can see, the default trace gives an administrator the ability to find the history of any DDL transaction. The default trace is a valuable tool for the modern DBA’s toolkit. It offers a wealth of information, while minimally impacting the system. The default trace is not a widely publicized feature of SQL Server 2005, but is slowly gaining fame. With this much information at their fingertips, administrators are more productive and can more easily identify problems in a production environment.

My recommendations are to look through the events and see what information already exists for your instances. Default trace should not only be used reactively but proactively. A proactive mentality will reveal small problems before they escalate to bigger problems.

References:

• List of available events: http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx
• How to enable default trace: http://msdn.microsoft.com/en-us/library/ms175513(SQL.90).aspx