TexCopy Sql Server 2005

This is one of the hidden features of SQL Server, by enabling this feature, sql server records all the traces running behind the scenes and in case, the server crashes, will record it into a file. Enabling this feature, is not a big task, but you have to perform these steps manually.

As we all knows, the first step is to Create a trace, that we can do this with 'sp_Trace_Create' stored procedure.

sp_trace_create [ @traceid = ] trace_id OUTPUT 

    , [ @options = ] option_value 

    , [ @tracefile = ] 'trace_file' 

    [ , [ @maxfilesize = ] max_file_size ]

    [ , [ @stoptime = ] 'stop_time' ]

In order to produce a 'Black-box', you must enable the option "TRACE_PRODUCE_BLACKBOX" (@Option = 8 ). Remember; please note that, this 'option' is incompatible with all the other options. Check books online for the detailed information.

DECLARE @TraceId INT;
EXEC sp_trace_create @TraceId OUTPUT, @options = 8;
SELECT @TraceId AS TraceID

The output is the trace which we created, in my case it is '1'

By default the status will be zero, so we need to manually enable this. In order to get the current status, you can run the system function fn_Trace_GetInfo()

SELECT * FROM ::fn_trace_getInfo(1)

the above will show you one similar output







Now, we need to find what this 'Property' and 'Value' fields are

The property of the trace as represented by the following integers:

1 - Trace Options, in this case we passes '8' as '@Options' , so the value for this will be '8'
2 - FileName, i.e. the exact path of the trace file
3 - MaxSize , Maximum size of the above trace file (in MBs, in this case it is 5MB)
4 - StopTime
5 - Current Trace status, a status value of zero means, the trace has not been started yet.

Since our trace is still in not started, we need to manually invoke it, the system procedure 'sp_trace_setStatus' will do this for us.

EXEC sp_Trace_SetStatus @TraceId  = 2 , @Status = 1 

Now In order to get the values from the trace file use

SELECT *
FROM ::fn_trace_gettable (
    'C:\Program Files\Microsoft SQL Server\MSSQL\Data\blackbox.trc', DEFAULT)