Manage Learn to apply best practices and optimize your operations.


Learn what sqldiag can do from within SQL Server in this excerpt from "SQL Server 2000 for experienced DBAs" by Brian Knight.

 SQL Server 2000 for experienced DBAs: Chapter 5, 'Monitoring and Tuning SQL Server' 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 BINN directory. This command-line utility gathers information about your SQL Server from several sources, and combines the data into one file that you can use to troubleshoot (including sending it to Microsoft Support). The following information is included in a text file named sqldiag.txt, which is in the Logs directory:

  • Last five SQL Server error logs
  • Registry information
  • DLL information and versions
  • Results of an sp_configure report
  • Results of sp_who and sp_lock
  • Inventory of all the system extended stored procedures
  • OS, network, and hardware configuration information
  • Last 100 queries run if black box is configured

    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.

    Black Box

    The black box records query information into a trace file called blackbox.trc in the Data directory. This file is much like the C2-Level Auditing file, except it's on a smaller scale. The black box writes the trace file in 128K blocks. This means that after you start SQL Server with the black box trace writing, the trace file appears as 0K until it reaches the first 128K or until SQL Server stops. To enable the black box, use the sp_trace_create and sp_trace_setstatus stored procedures with the parameter of 8 as shown here:

    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:

  • When the query executed
  • Login name
  • Application
  • Hostname
  • Error numbers with severity
  • Windows NT user name and domain
  • Database being connected to

    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 Log directory with the sqldiag.txt. The trace file is then renamed SQLDiag.trc.

    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.
  • Dig Deeper on Microsoft SQL Server Tools and Utilities

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.