Monitor system information with SQL Server 2005’s default trace

Sometimes it is difficult to diagnose problems on your SQL Server after they have occurred. So find out why you should use SQL Server 2005’s default trace feature to monitor certain events.

Introducing default trace

A trace is an activity that is run in the background on a SQL Server machine that captures specific events and data related to those events. This information is great for diagnosing performance problems, finding deadlocks, and auditing security information — just to name a few of its benefits.

Trace files are created and maintained in SQL Server through the TSQL language. You may be familiar with using SQL Server Profiler to diagnose performance issues. SQL Server Profiler is a front-end application that allows you to set up and monitor SQL Server with one or more traces through a graphical UI.

In SQL Server 2005, a default trace is always running in the background to monitor certain events. There is almost no overhead involved in maintaining this trace, and it can save you hours trying to figure out what is happening on your server. In fact, if you’re just now learning about default trace, you can still study your trace log files to diagnose recent problems with your SQL Server.

The trace log files roll over, allowing you to view historical trace data. This trace is fairly lightweight, which means that: It doesn’t use too many resources on your SQL Server; and it doesn’t capture every event that is happening on your server. The default trace captures information such as when the server starts and stops, failed login attempts, when objects are created and deleted, and when the log files grow and shrink. If you need to capture information than what the default trace is gathering for you, you may want to set up a separate trace to collect the data.
How to find default trace

You can set SQL Server trace files to be stored in a table in a database, as an XML file, or to a text file on a server. By default, the default trace saves event data to the LOG folder at the location of the SQL Server installation. If you don’t know where that is, there are a few system functions that you can use to figure it out.

The script below calls a system table-valued function that will return data for a specific trace running in the database or information for all traces running. The call I am making will return all of the traces in the system.

SELECT *
FROM fn_trace_getinfo(default)

If your system is currently only running the default trace, there is a good chance that the resultset returned from the above function call is similar to the resultset on my machine, as shown in the following table.

The above function tells me the name and location of where my default trace file is located on my database server. It also tells me that I am currently on my eighth log trace file. This means that there will likely be at least a few other trace log files in that folder that I can query for problems later if necessary.
Looking at the log data

You have two options for viewing the data from the trace log file. You can navigate to that location on the database server and click on the file; this will open the trace file up in SQL Server Profiler so you can view the information. From there, you can save the results to a table or XML document.

The second option is that you can copy this file path directly from the resultset and use it as a parameter to another system table-valued function that will allow you to directly query the data. I prefer this option because it lets me skip some steps, such as storing the data in the database to query it.

SELECT *
FROM fn_trace_gettable('C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGlog_8.trc',default)
ORDER BY starttime

The above function call returns all of the data from the trace file and sorts the data by the time the event occurred. With this ability, I can quickly look at the events that have occurred recently on my server to determine what is causing the problems.