sqldiag

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.



sqldiag

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.

    TIP
    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.


    NOTE
    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.
  • This was first published in August 2005

    Dig deeper on Microsoft SQL Server Tools and Utilities

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close