SQL Server: How SQL Diag Became Easy for Every DBA

SQL Diag is a useful diagnostic tool, through which we can collect lot of useful information, like

• Windows performance logs
• Windows event logs
• SQL Server Profiler traces
• SQL Server blocking information
• SQL Server configuration information 

Only problem with this tool is that you have to configure an XML file to provide information to SQL Diag that basically decides that what type of information you want. Though a default XML file is provided with tool as an example but it is not according to everyone’s requirement and somehow you have to change this XML file to get proper information.

Like me, most of DBAs and Developers are not good with XML, and that is why unable to use SQL Diag according to their needs. But recently this problem is solved by providing a simple SQL Diag Configuration utility, through which you can configure input XML file according to your requirements BUT just using your mouse and you need not to know a single line of XML.

You can download it from here.

SQL Diag Configuration Tool can be used for SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.

Provide your machine name , instance and a valid user credentials. But user must be a member of Windows Administrators group and a member of the SQL Server sysadmin fixed server role.

Select counters of your own choice and in the end save file as XML at your desired path

Next, on command shell type following line and press enter to start information collection process.

C:/> sqldiag /I "SD_Detailed.xml" /O "C:\SQLDiagOutPut" /N2

Here first part /I "D:\SQLDiag100.XML" is input XML file path, which we already generated through SQLDiag Configuration Tool. And second part /O "D:\SQLDiagOutPut" is path and folder name where you want to place your output files. While third parameter /N is an output folder management parameter.

There are two options for /N parameter

• N1: Automatically overwrite the output folder data

• N2: Do not overwrite existing data, rather it keeps history of existing output by automatically creating folder like SQLDiag_00000, SQLDiag_00001 and so on.