Data Management

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 other 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.

What's next?

The default trace is only a small sample of the power of running traces in SQL Server. In a future article, I will show you how you can create your own custom traces to run on your servers via TSQL.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

1 comments
sibir1us
sibir1us

Hey Tim, there are a few problems with the default trace: 1. the files may roll over way too fast on a busy server, since the files are only 5 and with a size of 20Mb. 2. The fn_trace_gettable function will give you results only from the trace file which is passed as a parameter onwards, which means that if you give a parameter of the current trace file it will take only that file and not the previous ones. Here is a more detailed article I wrote recently on the subject: http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/