Automating the collection of SQL Server diagnostic information

Automating the collection of SQL Server diagnostic information

I believe that many people have encountered problems when SQL Server, and you can not solve the need for DBA or Microsoft after-sales support engineer

To help solve the problem, they usually need you to collect some system information and SQL Server diagnostic information.

And the collection of this information requires a graphical tool, but also requires instructions, the steps are very complex.

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

Problem-solving Tools

This problem has been solved with the advent of a tool in SQL2005.

This tool can automatically collect a lot of information, expand the original application, enhance the ability of its information collection

This tool is SQLDIAG.exe SQL Rep: DIAG Representative Diagnostic (diagnostics)

SQLdiag This tool can collect information such as:

(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) Performance logs with both system and SQL performance counters

(6) server-side trace

This tool can help DBAs to automatically open server-side trace, which is much safer than with SQL Server Profiler .

Because SQL Server Profiler is a client-side tracing tool and consumes 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 install SQL Server is not the default path to choose may not be the same as me

Using tools

The tool needs to run in a command-line window, 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 ENTER to start, detailed steps to see

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

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

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

(1) SQL Errorlog file contents, 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 (LOG_XXX.TRC)

Using configuration Files

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

If you use both of these profiles, you can collect SQL Trace and Performance Logs.

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

The output file path needs to be specified in the sqldiag.

The example is entered under cmd:

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

I use sd_general.xml config file, configure F:\SQLDIAOUT as output directory, this folder does not need to be created beforehand, he helps you to create automatically.

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

Run the process to see SQLdiag open perfmon (Addingperfmon counters) and trace (starting Profiler Trace)

When the problem is tracked, press the CTRL + C tool to stop the log collection.

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

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's own gadgets

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.

DBAs can select one of them as needed

Disadvantages:

The only drawback of sqldiag is that it does not periodically query the System administration view DMV,

So it is recommended that automated information collection be SQLDIAG (using Sd_general.xml and Sd_detailed.xml for configuration files), plus some

System dynamic management view as a secondary

Automating the collection of SQL Server diagnostic information