Black box Trace in 2005

use master
go
-- Create a procedure
-- to call sp_trace_create
create procedure StartBlackBoxTrace
as
declare @tid int
exec sp_trace_create @tid output, 8
exec sp_trace_setstatus @tid, 1
go
-- Call it now to start
-- the black box trace



exec StartBlackBoxTrace
go



-- Mark the procedure for execution
-- at SQL Server startup
exec sp_procoption 'StartBlackBoxTrace',
'startup', true
go



SELECT * FROM fn_trace_getinfo (2);

This returns the following:
traceid property value
----------- ----------- ---------------------------------------------------------------------------------
2 1 8
2 2 \\?\C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\blackbox_1.trc
2 3 5
2 4 NULL
2 5 0

The various properties are:
1: the trace options. 8 means its a black-box trace.
2: the filename of the trace file. This isn't configurable for a black-box trace.
3: the maximum size of the file in MB. This also isn't configurable for a black-box trace.
4: the stop time for the trace. NULL means the trace won't stop until its manually turned off.
5: the current trace status. 0 is off, 1 is on.


Notice that the trace status is 0, which means that the trace hasn't been started - so we need to start it manually using:

EXEC sp_trace_setstatus @traceid = 2, @status = 1;




SELECT * FROM fn_trace_gettable (
'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blackbox.trc',
DEFAULT);
GO


http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx


A SQL Server 2000 & 2005 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.