Use event tracing to find the cause of a crash
When Microsoft introduced SQL Server 7.0, one of my favorite features was the SQL Server Profiler. I can say the same thing about SQL Server 2000. The differences between the SQL Server 7.0 version of the Profiler and the SQL Server 2000 version might not appear as great as those between SQL Trace in SQL Server 6.5 and SQL Server Profiler in SQL Server 7.0 because the UI still looks familiar. But Microsoft completely redesigned Profiler in SQL Server 2000 for improved performance, functionality, and scalability. I can't describe all of Profiler in this article, or even tell you about all the changes. Instead, I focus on one Profiler feature that has changed dramatically: the Blackbox.
The Blackbox contains a record of the most recent statements executed against the SQL Server system so that in case of a severe crash or other unexplained server behavior, you have a record of what queries ran recently and therefore a clue to what went wrong. In the sidebar "SQL Server's Black Box," April 2000, Itzik Ben-Gan described the creation and use of the blackbox.trc file in SQL Server 7.0. Although the Blackbox recording feature is separate from the tracing mechanism, the two use many of the same internal mechanisms. And, after a server crash, you can use Profiler to view the file that stores the Blackbox information, just as you can view any other trace file. Let's look at how to enable Blackbox recording in SQL Server 2000, using nearly the same mechanism that you use to set up any other server-side trace.
Gathering Information
SQL Server provides a special utility—the sqldiag command—to help you gather the information you need for troubleshooting SQL Server problems. You can find the utility in the same binn directory as all your other SQL Server executables; by default, the directory is \Program Files\Microsoft SQL Server\MSSQL\Binn. When you execute sqldiag from a command prompt while SQL Server is running, this utility gathers a vast amount of information about your OS, your hardware configuration, and your SQL Server configuration. Sqldiag writes the output to a file called sqldiag.txt (the default location is the \log folder of your SQL Server 2000 installation directory). If you enabled Blackbox recording, then executing sqldiag copies the Blackbox file to the same directory the sqldiag.txt file is in.
SQL Server 2000's sqldiag utility lets you specify several parameters to control certain aspects of the information gathering. The full list of parameters is in SQL Server 2000 Books Online (BOL), but the parameters relevant to this discussion are -X and -O output_file.
The -X parameter tells sqldiag not to include the error logs in the sqldiag.txt file. By default, the error log that SQL Server records restarts only when SQL Server starts, and the error log keeps growing until the next time you stop and restart the SQL Server service. By default, SQL Server maintains a history of the previous six error logs, so when you start the SQL Server service, the previous error log is copied into errorlog.1, the previous errorlog.1 is copied to errorlog.2, and so on. If you're gathering information to send to a support provider, then including the contents of several previous error logs can be a good thing. But for my own troubleshooting, I find that capturing the contents of all the error logs makes the sqldiag.txt file unwieldy. I rarely call sqldiag without the -X option.
The -O output_file parameter lets you specify a name other than sqldiag.txt for the sqldiag output. If you specify the -O option, sqldiag renames the blackbox.trc and blackbox_1.trc files based on the output_file name you use. For example, if you specify the output_file as diagreport.txt, sqldiag renames the trace files diagreport.trc and diagreport_1.trc, respectively.
Even if SQL Server isn't running when you execute sqldiag, the Blackbox trace file (if you enabled it) is still available. This availability can be useful if SQL Server is down because of an error; the Blackbox trace file will contain a record of which queries were running immediately before the failure. Running sqldiag before you restart your server can give you a clue to why the server stopped running.
Enabling the Blackbox
To start a trace from the server, you use the sp_trace_create procedure. This procedure usually takes up to five parameters, but when you're creating a trace for enabling Blackbox recording, you use only the first two. (A full discussion of creating server-side traces is beyond the scope of this article, but I'll discuss tracing again in future issues.)
The first parameter is an output parameter that returns a trace ID number if the Blackbox trace starts successfully. You need to save that trace ID value if you want to examine or manipulate the trace. The second parameter lets you specify one of several options to identify the trace type, but for the Blackbox, this parameter's value must be 8, which tells SQL Server to save the trace's output to a Blackbox file.
After you create a trace, you start it by executing the sp_trace_setstatus procedure, using a status value of 1. Starting the trace automatically captures a trace file called blackbox.trc in the default \data directory of your SQL Server installation. You can use a table-valued function called fn_trace_getinfo to look at the trace properties, including the exact name of the file. (A table-valued function is one that returns a rowset, so that you can invoke the function in the FROM clause of a SQL statement.) The following example code creates the Blackbox trace:
DECLARE @rc int, @TraceID int
EXEC @rc = sp_trace_create @TraceID output, 8
SELECT TraceID = @traceID, error = @rc
EXEC sp_trace_setstatus @traceID, 1
SELECT * FROM ::fn_trace_getinfo(@TraceID)
Figure 1 shows the trace information that the fn_trace_getinfo function returns.
Prev. page
[1]
2
next page 
