Information is collected by the SQL Server Connectivity Diagnostics Collector
This article describes the information that is collected by the SQL Server Connectivity Diagnostics Collector.
Original product version: SQL Server 2012, SQL Server 2008, SQL Server 2008 R2, SQL Server 2005
Original KB number: 2871695
Summary
The Microsoft SQL Server Connectivity Diagnostics Collector for Windows Server 2003 R2, Windows Vista, Windows Server 2008, Windows Server 2008 R2, Windows 7, Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 collects diagnostic information that is useful in troubleshooting a broad class of connectivity issues with SQL Server. The SQL Server Connectivity Diagnostics Collector also collects limited diagnostic information for SQL Server Analysis Services.
The SQL Server Connectivity Diagnostics Collector supports the following versions of SQL Server:
- SQL Server 2005
- SQL Server 2008
- SQL Server 2008 R2
- SQL Server 2012
Prerequisite software
There are different prerequisites to run diagnostic packages, depending on the operating system of the destination computer. The diagnostic will automatically check your computer for these prerequisites and start executing if they are already installed. Or, you are prompted to install the prerequisites if they are not already available on the computer. The Microsoft Automated Troubleshooting Service (MATS) may also install required software for you. For example, if Windows PowerShell is not present on the destination computer, MATS will install it automatically. For more information, see Information about Microsoft Automated Troubleshooting Services and Support Diagnostic Platform.
Required Windows rights
The SQL Server Connectivity Diagnostics Collector must be run by a user who has administrative rights on the computer on which the SQL Server Connectivity Diagnostics Collector is running.
SQL Server security requirements
The SQL Server Connectivity Diagnostics Collector discovers all instances of SQL Server that are installed on the computer on which the diagnostics tool is run. As part of the data-collection process, the SQL Server Connectivity Diagnostics Collector tries to connect to each instance of SQL Server that the diagnostic tool discovers to collect information about the current SQL Server configuration and server "state". Database connections are made by using Windows authentication. For the following diagnostic collection tasks to succeed, the user who is executing the SQL Server Connectivity Diagnostics Collector must have a Windows logon that is a member of the sysadmin fixed server role:
- SQL Server Always On Configuration Collection
- SQLDIAG Data Collection Scripts
Support for Windows failover clusters
To diagnose SQL Server Always On Availability Group connectivity or clustered SQL Server connectivity, you may have to run the SQL Server Connectivity Diagnostic Collector against more than one cluster node to collect all the necessary troubleshooting information as follows:
Run the SQL Server Connectivity Diagnostic Collector against the cluster node that currently owns the SQL Server Always On Availability Group or SQL Server cluster resource that is experiencing the connectivity issue.
Run the SQL Server Connectivity Diagnostic Collector against the node where a connectivity failure occurred previously. This enables the collection of various logs from the cluster node where the failure occurred previously.
Information that is collected
General information
Description File name Basic system information. This includes computer name, service pack number, computer model, and processor name and speed. <COMPUTER_NAME> System_Information.txt_ Virtualization information, and so on. <COMPUTER_NAME> DiscoveryReport.xml_ List of roles and features that are installed on server media. (Windows Server 2008 R2 and later versions) <COMPUTER_NAME> ResultReport.xml_ System log
Note
The SQL Server Connectivity Diagnostics Collector collects events from the past 15 days.
Description File name System log in TXT, CSV, and EVT or EVTX formats <COMPUTER_NAME> evt_System.csv_<COMPUTER_NAME> evt_System.txt_<COMPUTER_NAME> evt_System.evt_ or<COMPUTER_NAME> evt_System.evtx_ Application log
Note
The SQL Server Connectivity Diagnostics Collector collects events from the past 15 days.
Description File name Application log in TXT, CSV, and EVT or EVTX format <COMPUTER_NAME> evt_Application.csv_<COMPUTER_NAME> evt_Application.txt_<COMPUTER_NAME> evt_Application.evt_ or<COMPUTER_NAME> evt_Application.evtx_ Information about user and system environment variables on the destination computer
Description File name Information about user and system environment variables in the context of the current user on the destination computer in XML and TXT format <COMPUTER_NAME> EnvironmentVariables.xml_<COMPUTER_NAME> EnvironmentVariables.txt_ Information about all services that are installed on the destination computer
Description File name Information about services that are installed on the destination computer _<COMPUTER_NAME>SC_Services_Output.xml Information about filter drivers that are installed on the destination computer
Description File name Enumerate upper and lower filter drivers by using Fltrfind.exe _<COMPUTER_NAME>FltrFind.txt Report of mini-filter drivers
Description File name Enumerate mini-filter drivers by using Fltmc.exe _<COMPUTER_NAME>Fltmc.txt Information about all the running processes and driver details together with their file versions
Description File name Running drivers _<COMPUTER_NAME>sym_RunningDrivers.csv Running drivers _<COMPUTER_NAME>sym_RunningDrivers.txt %windir%\system32\drivers\*.*
_<COMPUTER_NAME>sym_Drivers.csv %windir%\system32\drivers\*.*
_<COMPUTER_NAME>sym_Drivers.txt Running processes _<COMPUTER_NAME>sym_Process.csv Running processes _<COMPUTER_NAME>sym_Process.txt Information about the networking configuration of the destination computer
Description File name Basic SMB configuration information such as output of net.exe subcommands such as net share, net sessions, net use, net accounts, and net config. _<COMPUTER_NAME>SMB-Info.txt Basic TCP/IP and networking configuration information such as TCP/IP registry key and outputs from ipconfig, netstat, nbtstat, and netsh commands. _<COMPUTER_NAME>TcpIp-Info.txt DNS Client hosts file _<COMPUTER_NAME>DnsClient_HostsFile.txt IPCONFIG/DISPLAYDNS command output _<COMPUTER_NAME>DnsClient_ipconfig-displaydns.txt NETSH DNSCLIENT SHOW STATE command output NOTE This command is not valid on Windows Server 2003 _<COMPUTER_NAME>DnsClient_netsh_dnsclient-show-state.TXT DNS Client registry entries <COMPUTER_NAME>DnsClient_reg.txt TCP/IP parameters registry key HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
_<COMPUTER_NAME>TcpIp_Parameters_Registry.xml Network adapter properties _<COMPUTER_NAME>NetworkAdapterConfigurations.xml Registry backup and text dump files of CurrentControlSet and SQL Server registry hives
Description File name HKLM\System\CurrentControlSet\SessionManagers
_<COMPUTER_NAME>CurrentControlSet_Reg.txt HKLM\SYSTEM\CurrentControlSet\Control\Lsa
_<COMPUTER_NAME>CurrentControlSet_Reg.txt HKLM\SYSTEM\CurrentControlSet
_<COMPUTER_NAME>CurrentControlSet_Reg.hiv HKLM\SOFTWARE\Microsoft\Microsoft SQL Server
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\MSSQLServer
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\Microsoft SQL Server 2005 Redist
_<COMPUTER_NAME>REG_SQL.txt HKLM\Software\Microsoft\MSFTESQLInstMap
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\Microsoft SQL Native Client
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\OLAP Server
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\SNAC
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\SQLXML4
_<COMPUTER_NAME>REG_SQL.txt HKLM\Software\Microsoft\Vsa
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\ODBC
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\MSDTS
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\MSXML 6.0 Parser and SDK
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\MSXML60
_<COMPUTER_NAME>REG_SQL.txt HKCU\Software\Microsoft\Microsoft SQL Server
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Microsoft\Microsoft SQL Server
_<COMPUTER_NAME>REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server 2005 Redist
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Native Client
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Native Client 10.0
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\Microsoft\SNAC
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\Microsoft\SQLXML4
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\Software\Wow6432Node\Microsoft\Vsa
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\ODBC
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt HKLM\SOFTWARE\Wow6432Node\Microsoft\MSDTS
_<COMPUTER_NAME>Wow6432Node_REG_SQL.txt Backup of HKLM\SOFTWARE\Microsoft\Microsoft SQL Server key in HIV format
_<COMPUTER_NAME>Microsoft_SQL_Server.hiv Output of the PSTAT utility
Description File name Output from PSTAT.EXE _<COMPUTER_NAME>PStat.txt Windows firewall information
Description File name Output of the netsh advfirewall show command together with various options _<COMPUTER_NAME>Firewall_netsh_advfirewall.txt Output of netsh advfirewall consec show rule name=all _<COMPUTER_NAME>Firewall_netsh_advfirewall-consec-rules.txt Output of netsh advfirewall export _<COMPUTER_NAME>Firewall_netsh_advfirewall-export.wfw Output of netsh advfirewall firewall show rule name=all _<COMPUTER_NAME>Firewall_netsh_advfirewall-firewall-rules.txt Output of netsh wfp show netevents _<COMPUTER_NAME>Firewall_netsh_wfp-show-netevents.txt HKLM\SOFTWARE\Policies\Microsoft\WindowsFirewall
<COMPUTER_NAME>Firewall_reg.txt HKLM\SYSTEM\CurrentControlSet\Services\BFE
<COMPUTER_NAME>Firewall_reg.txt HKLM\SYSTEM\CurrentControlSet\Services\IKEEXT
<COMPUTER_NAME>Firewall_reg.txt HKLM\SYSTEM\CurrentControlSet\Services\MpsSvc
<COMPUTER_NAME>Firewall_reg.txt HKLM\SYSTEM\CurrentControlSet\Services\SharedAccess
<COMPUTER_NAME>Firewall_reg.txt Information about user rights assignments on the destination computer
Description File name Local user rights assignments _<COMPUTER_NAME>UserRights.txt Information about the domain to which the destination computer is joined
Description File name Information about the domain to which the destination computer is joined. _<COMPUTER_NAME>DSMisc.txt Kerberos tickets and TGT
Description File name Kerberos tickets and TGT <COMPUTER_NAME> Kerberos_klist.txt_ Kerberos, LSA, and SChannel registry keys on the destination computer
Description File name HKLM:\System\CurrentControlSet\Control\Lsa
HKLM:\System\CurrentControlSet\Control\Lsa\MSV1_0
HKLM:\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
HKLM:\System\CurrentControlSet\Services\LanmanServer\Parameters
HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL
<COMPUTER_NAME> Authentication_Registry.xml_ Server network configuration of all instances of SQL Server on the destination computer
Description File name SQL Server network configurations (TCP/IP, NP, Shared Memory, and so on) for all instances of SQL Server. Also, (Database Engine) instances on the destination computer. This includes both 64-bit and 32-bit instances on a 64-bit computer. <COMPUTER_NAME> SqlServer_Network_Configurations.xml_ Active Directory properties and SPNs of SQL Server service accounts on the destination computer
Description File name Active directory properties and SPNs of SQL Server service accounts on the destination computer <COMPUTER_NAME> SqlServiceAccounts_SPN_ADProperties.xml_<COMPUTER_NAME> SQLInstances_Spn_Summary.xml_ SQL Server error logs
The SQL Server Connectivity Diagnostics Collector will collect up to 20 SQL Server error logs for each discovered instance that meets the following criteria:
- The size of each error log file must be 200 MB or less.
- The maximum total uncompressed size of all collected error log files cannot exceed 250 MB. When the 250-MB limit is reached, no additional error logs are collected for the instance of SQL Server.
Description File name Collects SQL Server error logs for all instances that are installed on the computer on which the diagnostic tool is executed. Named instance: <COMPUTER_NAME><INSTANCE_NAME>1033_ERRORLOG[.n] Default instance: _<COMPUTER_NAME>MSSQLSERVER_ERRORLOG[.n] Note
When the SQL Server Connectivity Diagnostics Collector is executed against a Windows failover cluster, SQL Server error logs are only collected if they are stored on a drive that is "owned" and "online" to the destination cluster node.
SQL Server Agent logs
The SQL Server Connectivity Diagnostics Collector will collect up to 20 SQL Server Agent logs for each discovered instance that meets the following criteria:
- The size of each SQL Server Agent log file must be 200 MB or less.
- The maximum total uncompressed size of all collected SQL Server Agent log files cannot exceed 250 MB. When the 250-MB limit is reached, no additional SQL Server Agent log files are collected for the instance of SQL Server.
Description File name Collects SQL Server Agent logs for all instances that are installed on the computer on which the diagnostic tool is executed. Named instance: <COMPUTER_NAME>_<INSTANCE_NAME >_1033_SQLAGENT.[OUT | n] Default instance: <COMPUTER_NAME>_MSSQLSERVER__1033_SQLAGENT.[OUT |n] Note
When the SQL Server Connectivity Diagnostics Collector is executed against a Windows failover cluster, SQL Server Agent logs are only collected if they are stored on a drive that is "owned" and "online" to the destination cluster node.
SQL Server minidump files
The SQL Server Connectivity Diagnostics Collector will collect up to 10 SQL Server minidump files for each discovered instance of SQL Server. The files are collected in descending order, based on the creation date of the minidump file. This means that the most recently generated files are collected first. The collected files must meet the following criteria:
The size of each minidump file must be 100 megabytes (MB) or less.
Each minidump file must be 30 days old or less.
The maximum total uncompressed size of all collected minidump files for a given instance of SQL Server cannot exceed 200 MB. When the 200-MB limit is reached, no additional minidump files are collected for the instance of SQL Server.
Note
All the files for a given instance are compressed into a zip archive before they are collected.
Description File name SQL Server minidump files Named instance: <COMPUTER_NAME><INSTANCE_NAME>1033_SqlMiniDumps.zip Default instance: _<COMPUTER_NAME>MSSQLSERVER_1033_SqlMiniDumps .zip A dump inventory report is generated and collected for each discovered instance of SQL Server. Named instance: <COMPUTER_NAME><INSTANCE_NAME>DumpInventory.log Default Instance: _<COMPUTER_NAME>MSSQLSERVER_DumpInventory.log Note
When the SQL Server Connectivity Diagnostics Collector is executed against a Windows failover cluster, SQL Server minidump files are only collected if they are stored on a drive that is "owned" and "online" to the destination cluster node.
SQLDIAG data-collection script
The SQLDIAG data-collection script will be executed against each instance of SQL Server that has a service status of "RUNNING." The script output is redirected to a file and collected by the diagnostic.
Description File name SQLDIAG script output Named instance: <COMPUTER_NAME><INSTANCE_NAME>1033_sp_sqldiag_Shutdown.out Default Instance: _<COMPUTER_NAME>MSSQLSERVER_1033_sp_sqldiag_Shutdown.out SQL Server Always On configuration information
Note
The SQL Server Always On configuration information is collected only from instances of SQL Server 2012.
Description File name SQL Server Always On configuration information Named instance: <COMPUTER_NAME><INSTANCE_NAME>1033_AlwaysOn.out Default Instance: _<COMPUTER_NAME>MSSQLSERVER_1033_AlwaysOn.out SQL Server Always On health logs
SQL Server Always On health session logs are collected from each instance of SQL Server 2012 that is installed on the destination computer. The files are collected and compressed into "instance specific" compressed archives.
The maximum number of SQL Server Always On Health logs that will be collected for each discovered instance is 20. The files are collected in descending order, based on the creation date of the file.
Description File name SQL Server Always On health logs Named instance: <COMPUTER_NAME><INSTANCE_NAME>AlwaysOn_health_XeLogs.zip Default Instance: _<COMPUTER_NAME>MSSQLSERVER_AlwaysOn_health_XeLogs.zip Note
When the SQL Server Connectivity Diagnostics Collector is executed against a Windows failover cluster, SQL Server Always On health logs are collected only if they are stored on a drive that is "owned" and "online" to the destination cluster node.
SQL Server failover cluster health logs
SQL Server failover cluster health logs are collected from each "clustered" instance of SQL Server 2012 that is installed on the destination computer. The files are collected and compressed into "instance specific" compressed archives.
The maximum number of failover cluster health logs that will be collected for each instance is 20. The files are collected in descending order, based on the creation date of the file.
Description File name SQL Server failover cluster health logs Named instance: <COMPUTER_NAME><INSTANCE_NAME>FailoverCluster_health_XeLogs.zip Default Instance: _<COMPUTER_NAME>MSSQLSERVER_FailoverCluster_health_XeLogs.zip Note
The SQL Server failover cluster health logs are collected only if they are stored on a drive that is "owned" and "online" to the destination cluster node.
SQL Server default system health logs
SQL Server default system health logs are collected from each instance of SQL Server 2012 that is installed on the destination computer. The files are collected and compressed into "instance specific" compressed archives.
Description File name SQL Server default system health logs Named instance: <COMPUTER_NAME><INSTANCE_NAME>system_health_XeLogs.zip Default Instance: _<COMPUTER_NAME>MSSQLSERVER_system_health_XeLogs.zip Note
When the SQL Server Connectivity Diagnostics Collector is executed against a Windows failover cluster, SQL Server default system health logs are collected only if they are stored on a drive that is "owned" and "online" to the destination cluster node.
Applies to
- SQL Server 2012 Business Intelligence
- SQL Server 2012 Developer
- SQL Server 2012 Enterprise
- SQL Server 2012 Express
- SQL Server 2012 Parallel Data Warehouse
- SQL Server 2012 Standard
- SQL Server 2012 Web
- SQL Server 2012 Enterprise Core
- SQL Server 2008 Developer
- SQL Server 2008 Enterprise
- SQL Server 2008 Express
- SQL Server 2008 Standard
- SQL Server 2008 Standard Edition for Small Business
- SQL Server 2008 Web
- SQL Server 2008 Workgroup
- SQL Server 2008 Express with Advanced Services
- SQL Server 2008 R2 Datacenter
- SQL Server 2008 R2 Developer
- SQL Server 2008 R2 Enterprise
- SQL Server 2008 R2 Express
- SQL Server 2008 R2 Express with Advanced Services
- SQL Server 2008 R2 Parallel Data Warehouse
- SQL Server 2008 R2 Standard
- SQL Server 2008 R2 Standard Edition for Small Business
- SQL Server 2008 R2 Web
- SQL Server 2008 R2 Workgroup
- SQL Server 2005 Developer Edition
- SQL Server 2005 Enterprise Edition
- SQL Server 2005 Enterprise X64 Edition
- SQL Server 2005 Evaluation Edition
- SQL Server 2005 Express Edition
- SQL Server 2005 Express Edition with Advanced Services
- SQL Server 2005 Standard Edition
- SQL Server 2005 Standard X64 Edition
- SQL Server 2005 Workgroup Edition
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for