Sunday, August 24, 2014

SQLDiag on Windows 8.1 Professional

SQLDiag utility collects diagnostics information from SQL Server(s) including System information. This utility was run against a SQL Server 2012 Developers edition on a Windows 8.1 Professional OS. It is available in all versions starting from SQL Server 2005 to SQL Server 2014.

SQLDIAG collects the following types of information:

•Windows Event Logs (Application, System, and Security) in .CSV files

•System Monitor (aka Perfmon) performance counters in .BLG file/s

•SQL Server Profiler traces in .TRC file/s

•SQL Server Blocking chains in a _BLK.TRC file

•SQL Server error logs, configuration, point-in-time snapshots of several DMVs in
a .OUT file

•System information via the Msinfo32 utility in a .TXT file
You can customize the utility by editing (manually)  the XML configuration file
of the utility, SQLDiag.xml
There is also a GUI tool which you can use to visually edit the xml file at the
codeplex site here.
http://sdct.codeplex.com/

File location on your computer:
If you have installed SQL Server in the present post SQL Server 2012 it should be
installed in the same directory as SQL Server which is usually,
%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn
Running it in console:
Open the command CMD preferably in Runas Administrator mode. Change the directory
to the one menitoned above.
------------
Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
C:\Users\Jayaram>cd %ProgramFiles%\Microsoft SQL Server\110\Tools\Binn
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>
---------------
Now type SQLDiag /? to get the help on SQLDiag Utility
-------------
Oops! on Windows 8.1 Professional instead of showing the help for this item, it
goes to User Access Control and if you choose to run it, the executbale is run as shown. Probably this is a bug.
Let us take up that problem later. The following console message is received  when the program starts:
2014/08/22 17:27:00.62 SQLDIAG Command line parameter C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLdiag.exe
2014/08/22 17:27:00.62 SQLDIAG Collector version 
2014/08/22 17:27:00.62 SQLDIAG
IMPORTANT:  Please wait until you see "Collection started" before attempting to reproduce your issue
2014/08/22 17:27:00.63 SQLDIAG Output path: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDIAG\
2014/08/22 17:27:00.83 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\OLAP\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\RS\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\MSSQLServer key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\MSSQLServer key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\Microsoft SQL Server\MSSQL11.REGENCYPARK key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Collecting from 1 logical machine(s)
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\OLAP\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening registry key  Software\Microsoft\Microsoft SQL Server\Instance Names\RS\MSSQLServer. Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Error opening Software key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\MSSQLServer key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\MSSQLServer key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.84 SQLDIAG Failed to open Software\Microsoft\Microsoft SQL Server\MSSQL11.REGENCYPARK key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.85 <computername>\* Instances registry value not found (you can usually safely ignore this) . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.85 <computername>\* Instances registry value not found (you can usually safely ignore this) . Function result: 259. Message: No more data is available.
2014/08/22 17:27:00.87 <computername>\* Instances registry value not found (you can usually safely ignore this) . Function result: 259. Message: No more data is available.
2014/08/22 17:27:00.87 <computername>\* Error opening MSSQLSERVER key . Function result: 2. Message: The system cannot find the file specified.
2014/08/22 17:27:00.87 <computername>\* Instances registry value not found (you can usually safely ignore this) . Function result: 259. Message: No more data is available.
2014/08/22 17:27:00.87 <computername>\* SQL Server version: 11
2014/08/22 17:27:00.88 <computername>\* Machine name: <computername> (this machine) 
2014/08/22 17:27:00.88 <computername>\* Target machine is not a cluster
2014/08/22 17:27:00.88 <computername>\* Instance: REGENCYPARK (64-bit)
2014/08/22 17:27:10.85 SQLDIAG Initialization starting...
2014/08/22 17:27:28.76 <computername>\* MsInfo: Get MSINFO32
2014/08/22 17:27:32.58 <computername>\* MsInfo: Get default traces
2014/08/22 17:27:36.72 <computername>\* MsInfo: Get SQLDumper log
2014/08/22 17:27:41.89 SQLDIAG Initialization complete
2014/08/22 17:27:41.89 <computername>\* Collecting diagnostic data
2014/08/22 17:27:42.89 SQLDIAG Collection started.  Press Ctrl+C to stop.
2014/08/23 20:34:00.35 SQLDIAG Ctrl+C pressed.  Shutting down the collector
2014/08/23 20:34:03.16 <computername>\* Shutting down the collector
2014/08/23 20:34:03.20 <computername>\* Getting SQLConfig report(s)
2014/08/23 20:34:30.15 SQLDIAG Ending data collection.  Please wait while the process shuts down and files are compressed (this may take several minutes)
In this case the collection was terminated and it can quite a long time.
The results are written to files found in the SQLDiag folder
C:\Users\Jayaram>%Program Files%\Microsoft SQL Server\110\Tools\Binn\SQLDiag