SQL Server black Box tracing--How do I enable black box tracing?

SQL Server black Box tracing--How do I enable black box tracing?

This trace is configured by setting the @options parameter value of the sp_trace_create to 8来. @options Specifies the option for tracking settings, when the setting value is 8, which is Trace_produce_blackbox, the last 5MB trace information record that the specified server produces will be saved by the server. Trace_produce_blackbox is incompatible with all other options. @tracefile Specifies the location and file name where the trace will be written, and cannot specify trace_file when using the Trace_produce_blackbox option.

The Trace_produce_blackbox has the following characteristics:

• It belongs to the rolling update trace. The default value for File_count is 2, but the user can override this value with the FileCount option.

• File_size As with other traces, the default is 5 MB and can be changed.

• The file name cannot be specified. File will be saved as:N '%sqldir%\mssql\data\blackbox.trc '

• Only the following events and their columns are included in the trace:

  • RPC starting

  • Batch starting

  • Exception

  • Attention

• You cannot add or remove events or columns from this trace.

• You cannot specify a filter for this trace.

The following Transact-SQL can be used to start a black-box trace:

DECLARE @TraceId intexec Sp_trace_create@traceid OUTPUT, @options = 8EXEC sp_trace_setstatus @TraceId, 1

Trace information can be returned through the fn_trace_getinfo function.

SELECT * FROM:: fn_trace_getinfo (@TraceID)

This trace is automatically configured to use two scrolling files, which will be flipped back and forth between two files when the default maximum file size is reached 5MB. However, some customers say that 5MB backup data is not enough to debug a crash failure. If you want to extend capacity, specify a value for the @maxfilesize parameter in addition to accepting a value (even null) for the @tracefile parameter. The following Transact-SQL code creates a black-box trace with a maximum capacity of 25MB:

DECLARE @TraceId intdeclare @maxfilesize bigintset @maxfilesize = 25EXEC Sp_trace_create@traceid OUTPUT, @options = 8, @tra Cefile = NULL, @maxfilesize = @maxfilesize EXEC sp_trace_setstatus @TraceId, 1

By default, the path to the black box trace file is in the default SQL Server data folder. However, as the file size is variable, the path can also be modified with the @tracefile parameter, if necessary.

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1582876

SQL Server black Box tracing--How do I enable black box tracing?