Automated collection of SQL Server diagnostic information tools selection and use introduction _mssql

Automated collection of SQL Server diagnostic information

Believe that many people have encountered SQL Server problems, and you can not solve the need for DBA or Microsoft After-sales support engineers to help solve the problem, then they generally need you to collect some system information and SQL Server diagnostics information.

And the collection of this information requires graphics tools, but also require instructions, the steps are very complex.

We need to follow step-by-step to complete the collection of information needed, in the process of collection, often toss a few times on the headache

Tools to solve problems

The problem was solved with the advent of a tool in SQL2005.

This tool automatically collects a lot of information, expands its original application, and enhances its ability to collect information.

This tool is SQLDIAG.exe SQL representative: SQLserver DIAG representative Diagnostic (diagnostics)

SQLdiag the information that this tool can gather is:

(1) Windows Event Log

(2) SQL Server ErrorLog, as well as configuration information, some important operational information

(3) SQL generated dump file

(4) Server System configuration information

(5) A performance log containing both system and SQL performance counters

(6) server-side trace

This tool can help the DBA to automatically open server trace, which is much more secure than using SQL Server Profiler

Because SQL Server Profiler belongs to the client-side tracking tool and requires system resources to communicate with the server

interprocess communication, and this tool uses server-side trace

The SQLdiag tool is installed by default in:C:\Program Files\Microsoft SQL Server\90\Tools\Binn

c disk is my SQL Server installation path, everyone installs SQL Server instead of choosing the default path it might not be like me.

Working with tools

This tool needs to be run at the Command Line window to run \sqldiag.exe directly on the command line, and the tool will use the default configuration to collect system information

Drag the SQLDIAG.exe to the cmd window, press the ENTER key can be started, detailed steps to see the figure

When appearing SQLDIAG Collection started. Press CTRL + C to stop. The information is then pressed crtl+cto terminate the execution of the tool.

The SQLDIAG tool generates three XML files in the Binn directory and generates a SQLDIAG subdirectory that holds the information just collected

Using the default configuration (that is, the SQLDiag.Xml in the Binn directory) will only receive:

(1) SQL Errorlog file content, as well as SQL configuration information and some important operational information (XXXX_SP_SQLDIAG_SHUTDOWN.OUT)

(2) SQL once generated dump record (Xxxx_sqldumper_errorlog.log)

(3) Server System configuration information (Xxxx_msinfo32.txt)

(4) SQL default trace File open (LOG_XXX.TRC)

Using configuration Files

You can use the other two sqldiag XML configuration Files Sd_general.xml and Sd_detailed.xml, which are in the same directory as SQLDiag.exe,

If you use both profiles, you can collect SQL Trace and the performance log.

However, if you want to use these two profiles the resulting file will be relatively large, placed in the SQL installation directory is no longer appropriate,

You need to specify the output file path in SQLdiag.

Examples are entered under cmd:

Format:sqldiag.exe/i <configure_file>/o <output_directory>

I use the Sd_general.xml configuration file, configure F:\SQLDIAOUT as the output directory, this folder does not have to be created in advance, he most help you create automatically

sqldiag.exe/i sd_general.xml/o F:\SQLDIAOUT

You can see SQLdiag open perfmon (Addingperfmon counters) and trace (starting Profiler trace) while running

When the problem is tracked, pressing the CTRL + C tool stops the log collection.

Some information is collected before the tool is closed, so it may take a while to completely stop

In the directory under the F:\SQLDIAOUT directory, you can see the following information

(1) SQL trace file (xxxx_sp_trace.trc)

(2) Windows Event log (Xxxx_applog_shutdown.txt,xxxx_seclog_shutdown.txt,xxxx_syslog_shutdown.txt)

(3) Windows Performance Log (SQLDIAG.BLG) with Performance Monitor can not open this file, to use the system from the small tool

Summarize

In fact, the main difference between the two XML configuration Files Sd_general.xml and Sd_detailed.xml is that Sd_detailed.xml collects more events in SQL Trace,

So the output will be much larger.

The DBA can select one of the following as needed

Disadvantages:

SQLdiag's only drawback is that it does not periodically query the System Management view of the DMV,

So it is recommended that automated information collection be SQLDIAG (using Sd_general.xml and sd_detailed.xml to do configuration files), adding some

System dynamic management view as auxiliary