Many Database Administrators are aghast when they discover that SQL Server 2005 and above have a default trace that runs 24 hours a day, 7 days a week. Most of us are taught early on in our careers that extended tracing is something that should only occur on rare occasions. Before you rush out and disable the default trace across all of your SQL Servers, let’s take a moment to find out what it’s actually capturing for us.

Is the Default Trace Running?

Before we begin, we need to determine a few things. First off, is the default trace running? You can use the following query to determine if the default trace is currently running on your system.

-- Is the default trace running
SELECT*
FROMsys.configurations
WHEREconfiguration_id = 1568

You can see from the output of this query that the default trace is indeed running and the necessary trace files will be available for us to access and investigate.

Uh, Oh! The Default Trace is Disabled!

If the Default Trace has been turned off by a wary Database Administrator, you can turn it back on using the following command. You must have enabled the “show advanced options.”

-- Turn on advanced options
sp_configure'show advanced options',1;
GO
RECONFIGURE;
GO
-- Turn on the default trace
sp_configure'default trace enabled',1;
GO
RECONFIGURE;
GO

Where are the Trace Files Located?

Now we know the default trace is running, we need to determine where the trace files are being stored on the system. The query listed below will return this information.

-- Get the path to the current trace rollover file
SELECT*
FROM::FN_TRACE_GETINFO(0)

The results of this query have provided the path to the current rollover trace file. We can see that the file is located inside the “Log” directory in the SQL Server installation path.

How Can I View the Trace File Information?

Now that we know where the rollover trace files are located, we can begin to investigate the information they contain. We could just double-click on the file listed above and open it in SQL Server Profiler. This would load the trace and show us its contents. I’d like to show you a more robust method of viewing this information.

Use the file path from above in the query that follows to inspect the contents of the trace as if it were a table in SQL Server.

SELECTloginname,
spid,
applicationname,
DatabaseId,
cat.name AS[CategoryName],
starttime,
e.name ASEventName
FROM::FN_TRACE_GETTABLE('C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLLoglog_12.trc',
0)
INNER JOINsys.trace_eventse ONeventclass =trace_event_id
INNER JOINsys.trace_categoriescat ONe.category_id =cat.category_id
WHEREcat.category_id =2

In this query I’m only returning events that occurred in the “Database” category. The “Database” category includes the events “Data File Auto Grow”, “Data File Auto Shrink“, “Log File Auto Grow”, “Log File Auto Shrink” and “Database Mirroring Status Change.”

You’ll see from the results above that there is a SQL Agent Job that causes a “Data File Auto Grow” every time it executes. This is probably something that warrants additional investigation.

In this case, I discovered a SQL Server database that was using a data file “Autogrowth” increment of 1 MB. This database was also receiving a substantial data load via a SQL Agent job every 1 second.

Conclusion

The SQL Server default trace captures 32 unique events across 6 categories. The example illustrated above is just a small sampling of the information that is available. I recommend you take some time to experiment with the other events and categories the next time you’re checking the health of your SQL Server environment. You never know what you might discover.

Scott Caldwell, SQL Server DBA