SQLDIAG – TroubleshootingSQL

SQLDIAG and SQL Server 2012

SQLDIAG is a data collection utility that is used for collecting T-SQL script output, perfmon data and profiler traces in a consolidated manner. This allows the database administrators for collect a single output without having to configure multiple data collection utilities for capturing the required data.

SQLDIAG has been shipping with the SQL Server product since SQL Server 2005. Now the reason I am writing this post is to talk about a specific issue that you can encounter when you already have a previous version of SQLDIAG installed on your machine along with SQL Server 2012.

Using the command below I am trying to execute a SQLDIAG data collection using a specific SQL Server 2012 SQLDIAG configuration file. The command that I used was to specify the output folder and the default SQLDIAG configuration file available at C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML:

C:\>sqldiag /O "F:\Temp\SQLDIAG Output" /I "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML"

The output that I got was:

SQLDIAG Output path: F:\Temp\SQLDIAG Output\

SQLDIAG Invalid SQL Server version specified. SQL Server version 11 is not supported by this version of the collector

SQLDIAG . Function result: 87. Message: The parameter is incorrect.

The reason for the above issue is that my environment PATH variable has the path for a previous version of SQLDIAG listed before the path of the SQL Server 2012 SQLDIAG. My path variable has the directory “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\” listed before “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\” which is the default location of the SQLDIAG utility. The PATH variable is updated with the SQL specific directories during a SQL Server installation. In my case, I have a SQL Server 2008 R2 instance installed on my box. So the configuration file which specifies a data collection for a SQL Server 2012 is failing because I am using a SQLDIAG from a SQL Server 2008 R2 installation.

If I executed the following command, then my SQLDIAG initialization will work correctly:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqldiag.exe" /O "F:\Temp\SQLDIAG Output" /I "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML"

To summarize, you need to full qualify your SQLDIAG executable path when collecting SQLDIAG data from a machine which has multiple versions of SQLDIAG installed on the machine.

Technorati Tags: SQL Server 2012,SQLDIAG,Data Collection,Tools

April 25, 2012

Tools Tips and Tricks #9: PSSDIAG Configuration Manager

I had been waiting for Diag Manager to be made public so that this post could go online. Diag Manager is a configuration tool that is used by CSS Engineers to configure a consolidated data collection for Windows Perfmon counters and SQL Server profiler trace (server-side). Additionally, the tool lets you add pre-configured custom diagnostics for Analysis Services configuration data, Mirroring configuration, Event log collection etc. and even custom data collection (using .sql scripts/custom utilities) which can be added using the Custom Diagnostics-> _MyCollectors option. (UI Screenshot below)

The tip that I want to talk about with this tool is that .CAB file generated uses a PSSDIAG.XML configuration file when using SQLDIAG to collect data. If you need to collect data from multiple different instances using the same configuration file, then you can just use the same extracted .CAB file contents with small tweaks to the SQLDIAG.XML file.

Changing the machine name from which the data needs to be collected: Change the machine name to the appropriate machine name.

<Machine name="MyLaptop">

Changing the instance name from which the data needs to be collected: Change the instance name value to the appropriate SQL Server instance name. MSSQLSERVER as instance name for default instance.

<Instance name="INST1" windowsauth="true" ssver="10" user="">

Disabling/Enabling perfmon data collection: Change the enabled option to true to enable perfmon data collection or false to disable collection. The maxfilesize parameter controls the perfmon file rollover size.

<PerfmonCollector enabled="true" pollinginterval="5" maxfilesize="256">

Changing the profiler trace collection and the rollover file size: Change the enabled value to true/false to enable/disable profiler data collection. Changing the maxfilesize parameter value controls the rollover file size for the profiler traces.

<ProfilerCollector enabled="true" template="_GeneralPerformance90.xml" pollinginterval="5" maxfilesize="350">

Collecting SQLDIAG output during startup/shutdown: This can be controlled using the enabled parameter value and the startup parameter value determines if the SQLDIAG output is to be collected during PSSDIAG startup/initialization. The shutdown parameter controls whether a SQLDIAG output needs to be captured once PSSDIAG collection is signaled to be stopped using CTRL+C.

<SqldiagCollector enabled="true" startup="false" shutdown="true" />

The other option that you have now that you have a GUI is to using Diag Manager to configure a new package for your target machine and click on Save. You will notice in the Save Configuration window, you have two locations that can be modified, one for the PSSDIAG.XML file and one for the .CAB file. Generate a new PSSDIAG.XML file and replace the existing file from a previously configured PSSDIAG with the newly generated PSSDIAG.XML for your new settings to take effect when the PSSDIAG launched again.

Another option that engineers use regularly for a long term PSSDIAG collection is the “Delete Old Trace Files” custom diagnostics. This is an option that is used for long term data collection. More information about this custom diagnostic is available in the FAQs section on Codeplex.

Note: The XML configuration for SQL Server 2008 and 2008 R2 can be mixed and matched using the ssver=”10.5” for SQL Server 2008 R2 instances and ssver=”10” for SQL Server 2008 instances. However, you shouldn’t use the same configured package across different platforms (x86/x64/ia64) and nor should you use the same package for different releases of SQL Server (2005/2008/2008 R2).

We have also documented some Common Issues faced while collecting data using a configured package. In case you have a question regarding the tool, feel free to post a new discussion. In case you find a defect that you need to report, you can log the defect under the Issue Tracker section.

Blog post talking about the release: http://blogs.msdn.com/b/psssql/archive/2011/05/24/pssdiag-sqldiag-configuration-manager-released-to-codeplex.aspx

This is a widely used tool in Microsoft Services for consolidated data collection while working on SQL Server performance issues. In case you have any feedback, please feel free to provide the same on the discussion forum for the tool on Codeplex.

del.icio.us Tags: Tools Tips and Tricks,Tools,PSSDIAG,SQLDIAG,Diag Manager

May 25, 2011

Tools Tips and Tricks #5: SQLDIAG and RANU

During the course of the Tools Tips and Tricks series, I had blogged about how to connect to a RANU instance and how to find out the SQL Express user instances active on the machine. Today, I shall discuss how to use SQLDIAG to collect data from a SQL Express user instance.

Since a SQL Express User Instance involves the dynamic spawning of a new instance and connections are allowed locally only using named pipes, normal instance discovery doesn’t work. If you need to collect diagnostic data using SQLDIAG for such an instance, you need to take the following steps:

1. Start the user instance by instantiating a connection to the user instance from the application that uses it.
2. Use any method mentioned in my previous post to get the instance GUID and the named pipe string.
3. Construct a named pipe alias using SQL Server Configuration Manager (see screenshot below). Use SQL Native Client Configuration 32-bit or 64-bit as appropriate for your version of SQL Express.

The Alias Name is in the form of <server name>\<user instance GUID>. The pipe name is what you obtained for the user instance that you are interested in monitoring. Leaver the Server name as blank.
4. Once the alias is created, test if you can connect to it using SQLCMD –S:.\BA78E627-AD14-4 –E locally from the machine that has SQL Express installed on it.
5. Now in the SQLDIAG.XML configuration file that you are using put the server name and instance name as follows:

After this you can run SQLDIAG from the machine which has SQL Express installed on it as user instances don’t accept remote connections.

Check back tomorrow for another new trick for tools that we use!

May 19, 2011

Tag: SQLDIAG

SQLDIAG and SQL Server 2012

Share this post:

Tools Tips and Tricks #9: PSSDIAG Configuration Manager

Share this post:

Tools Tips and Tricks #5: SQLDIAG and RANU

Share this post: