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