SQL Server black box Tracking--How do I configure in a production environment?

SQL Server black box Tracking--How do I configure in a production environment?

In case of intermittent failure, in order to make full use of the black box trace, it is necessary to ensure that when the server is running it is also running (after a planned or unplanned restart). To meet this requirement, you can set the black box trace to start automatically when SQL Server starts. First, the trace definition is encapsulated in a stored procedure in the primary database:

Use mastergocreate PROCEDURE startblackboxtraceasbegindeclare @TraceId intdeclare @maxfilesize bigintset @maxfilesize = 25EXEC Sp_trace_create@traceid OUTPUT, @options = 8, @tracefile = NULL, @maxfilesize = @maxfilesizeEXEC sp_trace_setstatus @TraceId, 1ENDGO

Then, set this procedure to start automatically when the SQL Server service starts:

EXEC sp_procoption ' startblackboxtrace ', ' STARTUP ', ' on '

If a crash event occurs, you can analyze the data collected to determine which stored procedures or queries are running at the time of the crash and (hopefully) identify what went wrong.

To stop and close the trace with trace ID 1, you can execute the following script:

EXEC sp_trace_setstatus 1, 0 exec sp_trace_setstatus 1, 2

Setting the status to 0 is stop tracking, and resetting the status to 1 is the restart trace. You can set the status to 2 to go off tracking, but you must have stopped it. You cannot restart a closed trace and you must completely recreate it. This blackbox.trc file is available when you close the trace. Then run sqldiag copy the full blackbox.trc file to the \log directory renamed to Sqldiag.trc.

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

SQL Server black box Tracking--How do I configure in a production environment?