Customize Black Block Trace
Customize Black Block Trace
By : Kasim Wirama, MCDBA
SQL Server has background running trace by default that records some events. It is useful when your SQL Server or its hosting environment had crashed, so you can look some queries and investigate them right before the failure. It listens for SP:Starting, SQL:BatchStarting, Exception and Attention events, with some columns for each of the events.
You can enable black box trace by setting @options parameter = 8 from system stored procedure sp_trace_create. And you run the event by executing sp_trace_setstatus with status = 1 and passing @traceId value, here is the script :
DECLARE @traceid INT;
EXEC sp_trace_create @traceid OUTPUT, @options = 8;
EXEC sp_trace_setstatus @traceid, 1
By default, it generates 2 trace files, and flip back and forth between them. Size of each is 5 MB and located in default DATA folder of SQL Server installation directory. For example here I change default size 5 MB to 10 MB into @maxfilesize parameter.
DECLARE @traceid INT, @maxfilesize BIGINT;
SET @maxfilesize = 10
EXEC sp_trace_create @traceid OUTPUT, @options = 8, @tracefile = NULL, @maxfilesize = @maxfilesize;
EXEC sp_trace_setstatus @traceid, 1
When you specify @maxfilesize, you need to pass value for @tracefile even though the value is NULL.
You can make your black box trace start automatically when SQL Server start by wrapping it inside a stored procedure and create the stored procedure in master database. Then you execute sp_procoption system stored procedure below :
EXEC sp_procoption ‘your stored procedure’, ‘STARTUP’, ‘ON’