While working on some scripts against SQL Server’s default trace, I wanted to develop a sure fire way to find the location of the default trace regardless of the version of SQL Server and without having to tinker with file names.

Since there are a few people that read this blog, I figured I’d share the methods I discovered.  Then go through the pros and cons of each method.  And finally, ask all of you if there is a method I’ve overlooked.

using sys.traces

The first method I turned to used sys.traces.  This catalog views lists all of the traces currently running on a SQL Server instance, this would include the default trace.  Since there are chance that multiple traces can be running at any one time, the trace that is most likely the default trace needs to be identified.  The query in Listing 1 uses a LIKE comparison to find the trace with the path that matches the default trace naming pattern; which is stored in the Log folder under MSSQL and has an initial file name of log.trc.

Trace files can, and will, roll over and when they do, SQL Server appends an underscore followed by a number that identifies its position in the list of trace files.  Some examples of this are  log_9.trc or log_101.trc; these are the 9th and 101st files in the series of trace files for the default trace.  Due to the rollovers in the file name, the name of the default trace file needs to be rolled back to the original value. In the query in Listing 1, this is done with REVERSE, CHARINDEX, LEN, and SUBSTRING functions.

-- Listing 1. Finding trace file location using sys.traces
SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc'
FROM sys.traces
WHERE path LIKE '%\MSSQL\Log\log%.trc';

While this method is accurate, it is a bit onerous in that obtaining the default trace file information requires a bit of work.  There is a query needed to get the value.  Followed by a rather complex use of functions to turn the value into the version that is needed.  For these reasons, I wanted to find something a bit more concise and easier to use with the function fn_trace_gettable.

Using sys.fn_trace_getinfo

The first alternative that found being recommend uses the function sys.fn_trace_getinfo.  This function returns information, such as the path, for a trace.  The most common way I’ve seen this used is with code Listing 2.

-- Listing 2. Recommended method for using sys.fn_trace_getinfo
SELECT [value]
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2

If you run the query in Listing 2, you’ll immediately notice some issues with it.  These issues are all similar to those that face sys.traces.  The query lacks proper identification of the default trace; never assume that the only trace running is the default trace.  Also, the value returned is the current, not the first default trace file.  Applying the logic from the query in Listing 1 to the query in Listing 2 results in the query in Listing 3 (hope I didn’t lose you).

-- Listing 3. Improved method for using sys.fn_trace_getinfo
WITH trace_info AS (
SELECT CONVERT(nvarchar(1000), [value]) AS path
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2)
SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc'
FROM trace_info
WHERE path LIKE '%\MSSQL\Log\log%.trc';

This method is as accurate as the first, but it doesn’t appear to be any better.  The best thing that can be said about this method is that it is more complicated.  Since we are often told to “keep it simple, stupid” – this isn’t really a preferred method for that reason alone.

Using the Registry

Our nerdery in finding the location for the default trace wouldn’t be complete without dipping into the registry at least once.  And there happens to be a method, which I’ve seen credited to Gert Drapers in a number of places, that can use the registry.  By going to the location of the setup values, stored at “Software\Microsoft\MSSQLServer\Setup”, the SQLDataRoot value can be obtained.  This folder is the base directory location where the DATA, BACKUP, and LOG folders are created.  Since the LOG folder is where the default trace is located, that folder can be used, as in Listing 4, to obtain the path and initial file name for the default trace.

--Listing 4. Method using registry
DECLARE @dir NVARCHAR(1000)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
SELECT @dir + N'\Log\log.trc';

From an accuracy perspective, this method seems rock solid.  There is no concern that another trace will interfere with the results.  Knowing the parent folder and appending the name is a quite simple process.  The main issue that I have with this method is that it uses a stored procedure.  While the stored procedure isn’t terrible, it prevents the inclusion of getting the default file location with any query that is going to use the results.  The end result just can run in a single T-SQL statement.

Using SEVERPROPERTY Function

In looking for a better solution, it came to light that the SERVERPROPERTY function has an undocumented option for the parameter ErrorLogFileName.  This parameter provides the location for the SQL Server errorlog; which just happens to sit alongside the default trace files. Knowing that allows us to replace the value ERRORLOG in the results and replace it with log.trc; which results in the location of the default trace.  This is shown in the code in Listing 5.

--Listing 5. Method using SERVERPROPERTY
SELECT REPLACE(CONVERT(VARCHAR(1000),SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG','\log.trc');

From an accuracy perspective, this appears to be the solution with the least likely chance for incorrect results.  No dependencies on making certain that the correct trace files are identified. There is also no dependency on the registry, which I try to avoid.  While some string parsing is required, it is nothing compared to the parsing from the first two methods.  In whole, the greatest risk with using this method is whether the parameter ErrorLogFileName will continue to be available within SQL Server.  For the time being, that seems an acceptable risk.

Conclusion

In this post, I reviewed the various ways that the location for the default trace can be determined.  There are quite a few ways and each has its risks for inaccuracies and complex processing to return the correct value.  My preferred method is using SERVERPROPERTY since it seems the most simple and accurate.  What do you think?