SQL server 2005 default trace

When SQL server 2005 is installed, it starts a default trace. Run the following query:


SELECT * FROM ::fn_trace_getinfo (0)


If you donot have any server side or sql profiler trace is ruuning, it still shows a trace.

This is the default trace SQL Server has started. The trace properties are as follow:


Property           Value

1,trace option      2 (TRACE_FILE_ROLLOVER)

2,trace file name   $Microsoft SQL Server$\MSSQL.1\MSSQL\LOG\log_??.trc;

3,max file size      20 MB

4,stop time         NULL

5,current status    1(running)


As the property says, the trace files are saved in the SQL server log folder. The file rolls over to a new file when its size reaches 20MB. The new file will have the same name as the previous file, but an integer will be appended to indicate its sequence (log_??.trc)


The default trace captures the following events: (1 is the default trace ID, it’s maybe different on your machine, Ref. enable/disable default trace)

SELECT * FROM fn_trace_geteventinfo (1)

It lists all the tracing events, e.g.:

18: Audit Server Starts and Stops

20: Audit Login Failed

22: ErrorLog

etc., altogether 32 events.

The default trace can be enabled or disabled by the configuration option "default trace enabled", which is an advanced option and takes effect immediately after change.

To disable it:




sp_configure ‘default trace enabled’, ‘1’;





sp_configure ‘show advanced option’, ‘0’;





SELECT * FROM sys.configurations

When enable it, the default trace is the next available trace id.


User cannot modify the default trace properties and status, e.g. (suppose 2 is the trace ID):

sp_trace_setstatus  @traceid =  2,  @status = 0

Msg 19070, Level 16, State 1, Procedure sp_trace_setstatus, Line 1

The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.


 MS suggests to enable the default trace. It can provide some auditing and debugging inforamtions if a DBA suspects server behaviour.


This entry was posted in SQL server 2005. Bookmark the permalink.

One Response to SQL server 2005 default trace

  1. ikechukwu okonkwo says:

    Wow ! thanks man this article helped.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s