SQL Server: Creating BlackBox kind of trace with TSQL

Published 28 May 08 04:59 AM | SQL Master 

Here is the kind of scripts defined within TEchnet articles about running black-box kind of traces against your SQL Server instance, then also you can  take advantage of the blackbox trace if you are facing intermittent problems, you want to make sure that it is always running when your server is running—including after either a planned or unplanned restart. To accomplish this, you can set the blackbox trace to start automatically when SQL Server starts. First, wrap the trace definition in a stored procedure in the master database:

 --Transact-SQL can be used to start a blackbox trace

USE master
GO
CREATE PROCEDURE StartBlackBoxTrace
AS
BEGIN
DECLARE @TraceId int
DECLARE @maxfilesize bigint
SET @maxfilesize = 25
EXEC sp_trace_create
@TraceId OUTPUT,
@options = 8,
@tracefile = NULL,
@maxfilesize = @maxfilesize
EXEC sp_trace_setstatus @TraceId, 1
END
GO

 Next, set the procedure to start automatically when the SQL Server service is started:

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

Comments

# Other SQL Server Blogs around the Web said on May 28, 2008 5:03 AM:

Here is the kind of scripts defined within TEchnet articles about running black-box kind of traces against

# SQL Server Transact-SQL (SSQA.net) : SQL Server: Creating BlackBox kind of trace with TSQL said on May 28, 2008 5:07 AM:

PingBack from http://sqlserver-qa.net/blogs/t-sql/archive/2008/05/28/4340.aspx

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.

Search

Go

This Blog

«May 2008»
SMTWTFS
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

Syndication