All you need to know about Default Trace – A Step by Step Guide

Default trace is a feature available from SQL 2005 onwards which is an enhanced replacement of SQL 2000 blackbox trace. Basically default trace is a server side trace.

Facts to note know about default trace:

1. This is a system generated server side trace which starts with SQL Server unless DBA explicitly stops it.
2. This trace captures vital information from the server and databases. However the maximum size of trace file stored is 20 MB and it stores up to 5 trace files at any point of time. This means it does delete the older data which can not be accommodated in the saved trace files.
3. Unfortunately there is no option to change or modify the settings of default trace. However DBA can stop or start default trace.
4. Default trace puts minimum overhead on server and can give very important information in case of a problem. Hence it is highly advisable not to stop default trace.

  

How to know if default trace is running on a Sql Server instance?

(How can I know if default trace is running on a Sql Server instance?)

Use below query to find the status of default trace on a sql server instance:

EXEC master.dbo.sp_configure 'show advanced options', 1;

GO

sp_configure

GO

If run value of “default trace enabled” is 1, that means default trace is running on the server.

Example:

Below example shows default trace is enabled on the server:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

name                                minimum     maximum     config_value run_value

----------------------------------- ----------- ----------- ------------ -----------

access check cache bucket count     0           65536       0            0

access check cache quota            0           2147483647  0            0

Ad Hoc Distributed Queries          0           1           0            0

affinity I/O mask                   -2147483648 2147483647  0            0

affinity mask                       -2147483648 2147483647  0            0

affinity64 I/O mask                 -2147483648 2147483647  0            0

affinity64 mask                     -2147483648 2147483647  0            0

Agent XPs                           0           1           1            1

allow updates                       0           1           0            0

awe enabled                         0           1           0            0

backup compression default          0           1           0            0

blocked process threshold (s)       0           86400       0            0

c2 audit mode                       0           1           0            0

clr enabled                         0           1           0            0

common criteria compliance enabled  0           1           0            0

cost threshold for parallelism      0           32767       5            5

cross db ownership chaining         0           1           0            0

cursor threshold                    -1          2147483647  -1           -1

Database Mail XPs                   0           1           0            0

default full-text language          0           2147483647  1033         1033

default language                    0           9999        0            0

default trace enabled               0           1           1            1

… output truncated

Where the default trace file stored etc?
(How can I know where the default trace files are stored?)

Use below query to find the vital information about default trace like:

Where are the default trace files stored?

Is default trace running on my server?
What is the stop time of default trace?
What is the maximum file size of default trace?

SELECT *   FROM ::fn_trace_getinfo(default)

You can read the output of this query using below table:

Column name	Data type	Description
traceId	int	ID of the trace.
property	int	Property of the trace: 1= Trace options. For more information, see @optionsin sp_trace_create (Transact-SQL). 2 = File name 3 = Max size 4 = Stop time 5 = Current trace status (1 = Started, 0 = Stopped)
value	sql_variant	Information about the property of the trace specified.

Example:

Below example is an output from a test SQL 2008 R2 Server:

traceid     property    value

----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1           1           2

1           2           G:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_26.trc

1           3           20

1           4           NULL

1           5           1

Result’s interpretation:

Trace ID is 2 (Value = 2)

Present trace file is G:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_26.trc. This means you can get other historical trace files from G:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ ((Value = G:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_26.trc)

Maximum size of the trace file is 20 MB (Value = 20)

There is no stop time on the trace (Value = NULL)

Current status of trace is running (Value = 1)

How to read the trace data?

(How can I read default trace date or output?)

Most recommended way to read the trace data is to grab the trace file and analyze it by opening with SQL Server Profiler. This will give you flexibility and detailed view of all events captured by default trace.

However easiest way to retrieve data from default trace using standard reports from SQL Server Management Studio. For example, below screen shot shows how to find the schema change history of a specific database. This data is retrieved from default trace.

How to stop default trace?

(How can I stop default trace?)

(How can I disable default trace?)

Execute below code to immediately stop default trace:

EXEC master.dbo.sp_configure 'allow updates', 1;

GO

EXEC master.dbo.sp_configure 'show advanced options', 1;

GO

EXEC master.dbo.sp_configure 'default trace enabled', 0;

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC master.dbo.sp_configure 'show advanced options', 0;

GO

EXEC master.dbo.sp_configure 'allow updates', 0;

GO

Note:

Do not disable default trace unless you have a valid reason. Also this change will take effect immediately.

How to start default trace?

(How can I start default trace?)

(How can I enable default trace?)

Execute below code to immediately start default trace:

EXEC master.dbo.sp_configure 'allow updates', 1;

GO

EXEC master.dbo.sp_configure 'show advanced options', 1;

GO

EXEC master.dbo.sp_configure 'default trace enabled', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC master.dbo.sp_configure 'show advanced options', 0;

GO

EXEC master.dbo.sp_configure 'allow updates', 0;

GO

Note:
This change will take effect immediately.

How to tell what is the oldest data available from default trace?

(How can I know what is the oldest data available from default trace?)

Most recommended way to find this information is to open the oldest trace file and then by browsing to oldest available record. However for a quicker and easier option, you can open a standard report and get the information easily from SQL Server Management Studio.

For example, below report is actually generated from default trace and informs that data is available since 2/4/2011 10:15:59AM)

Is it recommended to disable default trace?

(Should I disable default trace?)

  

The short answer to this question is NO. Default trace is very light weighted trace and does not really overhead the server in an extent to cause performance problem. Hence it is highly recommended to keep default trace running.

In a very unusual situation if you conclude that server’s performance is impacted by default trace and stopping it will help to improve the performance then may need to disable it. In other words, you should have a very strong business and technical reason to stop it. However please note that if you disable or stop default trace, you may be lacking vital information while working on a serious problem or trouble which can put you in deeper trouble.