Adding Your Own Custom Collectors
Now that you have looked at the myriad
of pre-configured data collectors for various scenarios and features,
you might be wondering what happened to the specific data collection
that was required for an issue you were troubleshooting in your SQL
Server environment. If the previously described custom collectors do
not satisfy your data collection requirements, then you can configure
your own custom collector using the _MyCollectors custom collector.
Consider an example in which you needed to
collect the space usage of the folders in the default data folder of
SQL Server. This information was to be collected during startup of the
data collection using a PowerShell script. The following PowerShell
Script will capture the disk space usage for all the folders in the
folder specified below:
$startFolder = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA'
$colItems = (Get-ChildItem $startFolder | Measure-Object -property length -sum)
'$startFolder -- ' + '{0:N2}' -f ($colItems.sum / 1MB) + ' MB'
$colItems = (Get-ChildItem $startFolder -recurse | Where-Object
{$_.PSIsContainer -eq $True} | Sort-Object)
foreach ($i in $colItems)
{
$subFolderItems = (Get-ChildItem $i.FullName |
Measure-Object -property length -sum)
$i.FullName + ' -- ' + '{0:N2}' -f ($subFolderItems.sum / 1MB) + ' MB'
}
Assume that the preceding code was saved into a file called space.ps1. Then you would right-click on _MyCollectors and add a Utility task as shown in Figure 9.
When data collection is completed, a file named <Machine Name>__MyCollectors_Get_Disk_Space_Shutdown_DiskSpace.OUT
would appear in the output folder. If you looked at the configuration
file after saving the settings using Diag Manager, you would find that
the XML node that has the custom collector configuration would look
like the following code snippet:
<CustomDiagnostics>
<CustomGroup name=’_MyCollectors’ enabled=’true’ />
<CustomTask enabled=’true’ groupname=’_MyCollectors’ taskname=’Get Disk
Space’ type=’Utility’ point=’Shutdown’ wait=’OnlyOnShutdown’ cmd=’powershell.exe -File
".\space.ps1" -NoLogo -NonInteractive > "PShell_DiskSpace.OUT" ’
pollinginterval=’0’ />
</CustomDiagnostics>
NOTE Before saving the package configuration, you must add your custom scripts (in the preceding example, space.ps1) to the C:\Program Files\Microsoft\Pssdiag\CustomDiagnostics\_MyCollectors folder.
Saving and Using a SQLdiag Configuration
Now that you know how to configure the
SQLdiag collection based on the data you need to collect, this section
describes how to save the configuration package, as this tool was
released to work with SQL Server 2008 R2, 2008, and 2005 versions. When
you click the Save button, the dialog shown in Figure 10
will appear, providing the location of the cabinet (.cab) file and the
XML configuration file. You can change the path of the two files as
appropriate.
When you click OK, the dialog shown in Figure 11
will appear for selecting either SQL Server 2008 R2 or SQL Server 2008,
in case you were configuring the data collection using the SQL Server
2008 tab in the Diag Manager.
NOTE The
preceding configuration steps work for all releases of SQL Server 2005
and later. Because we are discussing data collection configuration for
SQL Server 2012 instances, you should select the SQL Server 2008 tab
when you start your configuration. When the dialog is provided for
choosing the SQL Server version while saving the cabinet file, choose
the SQL Server 2008 R2 option.
By default, the cabinet file generated is located at C:\Program Files\Microsoft\Pssdiag\Customer with the name pssd.cab.
This cabinet file stores all the necessary supporting files required to
collect the data for all the enabled data collectors configured by you.
The pssd.cab file can now be copied to the target machine from which you want to collect the diagnostic data.
When you extract the cabinet file, you will find a pssdiag.cmd file, which calls the SQLdiag executable to collect data using the PSSDIAG.XML configuration file. Before you start the data collection, you need to modify the PSSDIAG.XML file, changing the ssver
value to 11 from 10.50, as shown in the following example. This directs
the SQLdiag utility to collect diagnostic data from a SQL Server 2012
instance.
<Instance name='MSSQLSERVER' windowsauth='true' ssver="11" user=''>
Now you are all ready to collect diagnostic data using SQLdiag by executing the pssdiag.cmd , which is a DOS batch command file, to start the data collection.
WARNING If
you had a SQL Server instance from a release earlier than SQL Server
2012 already installed on the machine before the RTM version of SQL
Server 2012 was installed, then you need to do one of two things.
Either edit your environment PATH variable
to ensure that the SQL Server 2012 SQLdiag path precedes any other
directories containing older versions of the SQLdiag executable, or
modify pssdiag.cmd and hard-code the SQL Server 2012 SQLdiag path.
