Mitch Wheat - Treat the cause, not the symptoms!

Most people are aware that when SQL Server is installed, it starts a lightweight background trace known as the default trace. If this has not been disabled (and it’s unlikely that it will have been), this trace will be running with a trace ID of 1:

SELECT * FROM sys.traces

This trace includes a small set of events for server starts and stops, object deletion and creation, log and data file autogrowth and other changes at the database level. One of the things this trace is useful for is unexpected events, such as finding out who dropped a table. You can examine the default trace’s contents in the same way as any other trace using fn_trace_gettable():

DECLARE @path varchar(256)

SELECT @path = path
FROM sys.traces
where id = 1

SELECT *
FROM fn_trace_gettable(@path, 1)

Another less known background trace that comes preconfigured with SQL Server 2005 is the BlackBox trace. Its primary use is in diagnosing intermittent server crashes and can be started by setting the @options parameter of sp_trace_create to 8. This trace uses 2 rollover files and toggles between them as one reaches its maximum size, which is configurable. A great tip from Chapter 2 of “Inside Microsoft SQL Server 2005: Query Tuning and Optimisation” is to wrap the blackbox trace definition in a stored procedure, and configure the stored procedure to run when SQL Server starts (that way intermittent problems are more likely to be captured after restarts):

USE master
GO
CREATE PROCEDURE StartBlackBoxTrace
AS
BEGIN
DECLARE @TraceId int
DECLARE @maxfilesize bigint
SET @maxfilesize = 25 –- 25MB maximum file size
EXEC sp_trace_create
@TraceId OUTPUT,
@options = 8,
@tracefile = NULL, -- NULL = default SQL Server data file folder location: you might want to change this…
@maxfilesize = @maxfilesize
EXEC sp_trace_setstatus @TraceId, 1
END
GO

Set the procedure to start automatically when SQL Server is started:

EXEC sp_procoption 'StartBlackBoxTrace', 'STARTUP', 'ON'