 |
|
sqldiag |
|
| 14 Aug 2005 | SearchSQLServer.com |
|
|
 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.
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
