The following excerpt, courtesy of McGraw-Hill Osborne Media, is from Chapter 5 of the book "SQL Server 2000 for experienced DBAs" written by Brian Knight. Click for the complete book excerpt series or purchase the book.
Beginning with SQL Server 7.0, Microsoft ships a handy tool called sqldiag.exe, which can be found in the
For optimal use, SQL Server must be running when you execute sqldiag. If SQL Server is not running, some information is skipped. If you are trying this from a Windows 98 workstation, you must use the -U and -P parameters for the user name and password, respectively.
It is a good idea to generate one of these reports right after you build your server so you can take a snapshot of the server configuration.
The black box records query information into a trace file called blackbox.trc in the
DECLARE @TraceID int
EXEC sp_trace_create @traceid output, 8
EXEC sp_trace_setstatus @traceid, 1
This trace records every query executed on the server, as well as any exception errors that occur. You can view the trace in Profiler after it is closed. The trace also records the following information:
The trace automatically stops and closes when SQL Server stops. The SQL Server stop can be clean or abrupt (a crash). You can also stop and close the trace manually by using the sp_trace_setstatus stored procedure:
EXEC sp_trace_setstatus 1,0
EXEC sp_trace_setstatus 1,2
The trace can be used to diagnose a server problem and to determine what is causing your server to crash. If you want the black box to start automatically when SQL Server starts, you can make the query that creates the black box into a startup stored procedure. When sqldiag.exe executes, the last 100 queries in the trace file are copied over to the
Since the black box takes valuable CPU resources away from your SQL Server, it can slow your server down. Keep the black box running only when you are troubleshooting.
Click to return to the main page: Monitoring and tuning SQL Server
Click for the complete book excerpt series.