Systems Engineering and RDBMS

Black box trace in SQL Server 2000 and Default Trace in 2005

Posted by decipherinfosys on December 26, 2007

SQL Server 2000 used to have a black box trace which could be used to diagnose and troubleshoot issues with SQL Server. The name probably stems from the flight data recording black box. It was typically used to diagnose AV (Access Violations) or crashes though it could be used for tuning purposes as well like capturing the trace information and feeding it to the index tuning wizard in SQL Server 2000. This used to be one of the things that MSFT PSS always used to ask for in order to troubleshoot crashes. A SQL Server 2000 black box trace used to capture the following events:

1) Stored Procedure Execution (RPC:Starting),

2) T-SQL Batch Execution (SQL:BatchStarting), and

3) Errors and Warnings (Attention and Exception)

And for those events, the information that was captured was:

1) Date and Time of execution,

2) The name of the application, the name of the database, the name of the server/workstation from where the call originated and the name of the user that executed the query/procedure

3) The query and the error that occurred.

This trace file was written to the data directory i.e. the “X:\Program Files\Microsoft SQL Server\MSSQL\data” directory where X would be your logical drive where you have installed SQL Server. The file that used to be created was blackbox.trc and it was written to in 128KB chunks and it rolls over to a new file after 5MB limit. This black box trace was started by using the system stored procedure: sp_trace_create. The trace type needed to be set to the trace status of 8 to indicate that it is a black box trace. Example:

declare @ID int
exec sp_trace_create @ID output, 8
exec sp_trace_setstatus @ID, 1

This was a very useful tool in SQL Server 2000. This still works in SQL Server 2005. However, now there is also a default trace. This does not have much of an impact on the instance but you should be aware of such a default trace being collected on your behalf :-)

In order to see this trace, you can read the data from the function: fn_trace_get_info() and pass in the value of default to it.

SELECT * FROM fn_trace_getinfo(default);

On my instance, I get:

traceid     property    value
----------- ----------- --------------------------------------------------------------------------
1           1           2
1           2           C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_15.trc
1           3           20
1           4           NULL
1           5           1
2           1           8
2           2           \\?\C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\blackbox.trc
2           3           5
2           4           NULL
2           5           0

And this is because, I am running a black box trace as well as the default trace is also running on the instance. The black box trace is the one with traceid value of 2 and the default trace is the one with the traceid value of 1. The 5enumerated values in the property column represent: trace options, file name of the trace, maximum size of the trace file, stop time for the session and the current status of the trace – a value of 0 indicates off and 1 indicates on. As you can see from above, I have stopped the black box trace but the default trace is going on.

Unlike the black box trace, this trace file that gets generated by the default trace gets stored in the LOG directory:

X:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

You can also check for this configuration using sp_configure: When you run sp_configure (after enabling the “show advanced options”), you can see this in the parameter: “default trace enabled”:

EXEC master.dbo.sp_configure ‘allow updates’, 1;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ‘default trace enabled’;
GO

Configuration option ‘allow updates’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.

name minimum maximum config_value run_value

———————————– ———– ———– ———— ———–

default trace enabled 0 1 1 1

So, what does this default trace capture? Best way to look at all the events and the data that gets captured is to go to the LOG directory and open it up in profiler. Then, after that click on the properties tab to see what the trace captures. See the jpg image below for such a snapshot:

dt_1.jpg

Another question that would come to mind after reading this is whether one should disable this trace? especially in production environment(s). That depends upon your environment. We would recommend that you take a look at the default trace events and columns that get captured and then keeping your production environment in mind, make a decision. Do make a point to clear out the older trace files if you do keep the trace running.

About these ads

2 Responses to “Black box trace in SQL Server 2000 and Default Trace in 2005”

  1. [...] on December 6, 2008 We had covered black box trace and the default trace before in our post here. In response to that post, one of the reader made a good point about the usefulness of the default [...]

  2. [...] on December 6, 2008 We had covered black box trace and the default trace before in our post here. In response to that post, one of the reader made a good point about the usefulness of the default [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: