Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: Default Trace

SQL Server Profiler is a useful tool for troubleshooting and monitoring database and server performance. Usually, a DBA has to manually setup a trace to monitor a specific area of interest (e.g. Deadlocks, Login audit). However, many are unaware that a default Profiler trace is permanently running on the majority of servers. It is possible to query the trace files from within SQL Server. The default trace captures information such as file growth, mirroring status, object creation/deletion/alteration and security auditing, amongst other items. If you decide that this is not valuable information then you can switch off the default trace.

To check to see whether a default trace is indeed currently running, execute the following:-

FROM sys.configurations 
WHERE configuration_id = 1568

If the value is ‘1’ then the trace is running. Once you have established that the trace is enabled, you need to run a query to determine the most recent trace file. Trace files automatically rollover, so you need to know which one to query.

SELECT traceid, value 
FROM [fn_trace_getinfo](default) 
WHERE [property] = 2;

The full path and file name will be returned. You can then run the following query and include the correct path to the most recent file. You could of course setup a variable to automatically populate the path.

FROM [fn_trace_gettable]('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_49.trc', DEFAULT) 
ORDER BY StartTime;

You can disable the trace by issuing the following commands. However, don’t expect a dramatic increase in server performance as the trace uses only minimal resources.

EXEC master.dbo.sp_configure 'allow updates', 1;
EXEC master.dbo.sp_configure 'show advanced options', 1;
EXEC master.dbo.sp_configure 'default trace enabled', 0;
EXEC master.dbo.sp_configure 'show advanced options', 0;
EXEC master.dbo.sp_configure 'allow updates', 0;

Tags: , , , ,

%d bloggers like this: