Tools for automatic collection of SQLSERVER diagnostic information and usage

Automatic Collection of SQLSERVER diagnostic information

I believe that many people have encountered problems with SQLSERVER, and you cannot solve the problem by requiring DBA or after-sales support engineers from Microsoft, then they usually need to collect some system information and SQLSERVER diagnostic information.

The collection of such information requires graphical tools, instructions, and complicated steps.

We need to collect all the required information step by step. During the collection process, we often have a headache after several times.

Troubleshooting tools

This problem was solved with the emergence of a tool in SQL2005.

This tool can automatically collect a lot of information, expand the original application, and enhance the ability to collect information.

This tool isSQLDIAG.exeSQLRepresentative:SQLSERVERDIAGRepresentativeDiagnostic)

The SQLDiag tool collects the following information:

(1) Windows Event Log

(2) SQLSERVER ErrorLog, SQL configuration information, and some important running information

(3) DUMP files generated by SQL

(4) server system configuration information

(5) Performance Logs containing both system and SQL Performance counters

(6) server Trace

This tool can help DBAs automatically openServer Trace, ComparisonSQLSERVER ProfilerMore secure

Because SQLSERVER Profiler is a client tracking tool and consumes system resources to communicate with the server

Inter-process communication, and this tool usesServer Trace

The SQLDiag tool is installed on:C: \ Program Files \ Microsoft SQL Server \ 90 \ Tools \ Binn

Disk C is the installation path of my SQLSERVER. If you do not select the default path for installation of SQLSERVER, it may be different from that of me.

Tools used

This tool must be run in the command line window. You can run \ SQLdiag.exe directly on the command line. The tool uses the default configuration to collect system information.

Drag sqldiag.exe to the cmd window and press enter to start it. For more information, see

WhenSQLDIAG Collection started. Press Ctrl + C to stop. PressCrtl + CTo terminate the execution of this tool.

The SQLDIAG tool generates three XML files under the Binn directory and generates a SQLDIAG subdirectory to store the information just collected.

 

If you use the default configuration (SQLDiag. xml in the Binn directory), you will only receive:

(1) content of the SQL errorlog file, as well as SQL configuration information and some important running information (XXXX_sp_sqldiag_Shutdown.out)

(2) dump records generated by SQL (XXXX_SQLDUMPER_ERRORLOG.log)

(3)server system configuration information (xxxx_msinfo32.txt)

(4) Default Trace file enabled by SQL by Default (log_XXX.trc)

Use the configuration file

You can use the XML configuration files SD_General.xml and sd_detailed.xmlthat come with SQLDiag. These XML files are in the same directory as sqldiag.exe,

If you use these two configuration files, you can collect SQL Trace and Performance Logs.

However, if you want to use these two configuration files, the files generated will be relatively large, and it is no longer appropriate to put them in the SQL installation directory,

You must specify the output file path in SQLDiag.

Enter the following example in cmd:

Format:SQLdiag.exe/I <configure_file>/O <output_directory>

I use the SD_General.XML configuration file and configure F: \ SQLDIAOUT as the output directory. This folder does not need to be created in advance. It will automatically create

SQLdiag.exe/I SD_General.XML/O F: \ SQLDIAOUT

 

During running, you can see that SQLdiag enables Perfmon (AddingPerfmon counters) and Trace (Starting Profiler Trace)

After the problem is tracked, press Ctrl + C to stop log collection.

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

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

(1) SQL Trace file (XXXX_sp_trace.trc)

Token)

(3) For Windows Performance Logs (SQLdiag. blg), the file cannot be opened with the performance monitor. You need to use the built-in gadgets of the system.

Summary

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,

Therefore, the output will be much larger.

DBA can select one of them as needed

Disadvantages:

The only drawback of SQLdiag is that it does not regularly query the management view DMV,

Therefore, we recommend that you use SQLdiag for Automatic Information Collection (using SD_General.xml and SD_Detailed.xml for configuration files ).

System Dynamic management view as an aid