Consolidating Data Capture with SQLdiag - SQL Server 2012 Internals and Troubleshooting - page 314

WARNING The T-SQL scripts used by the aforementioned collectors make
use of the xp_cmdshell extended stored procedure, which will result in errors
reported in the SQLdiag log i le if xp_cmdshell is not enabled on the target
SQL Server instance. If xp_cmdshell cannot be enabled on the target SQL
Server instance due to compliance or security reasons, then the information that
couldn't be collected can be manually collected.

The SQL 2005 tempdb Space and Latching custom collector can be used to troubleshoot SQL
Server issues pertaining to tempdb contention. The T-SQL script used by this collector collects
tempdb usage and statistics in a loop during the entire duration of the SQLdiag data collection.
The collector uses the following DMVs and system catalogs to collect the diagnostic information:
➤

sys.dm_db_file_space_used

➤

sys.dm_db_session_file_usage

➤

sys.dm_db_task_space_usage

➤

sys.sysprocesses

➤

sys.dm_os_waiting_tasks

The SQL Blocking custom collector enables the trace l ag 1222 during the initialization phase of
SQLdiag, which ensures that deadlock information is written to the SQL Server Error Log. Note
that this collector does not collect blocking information from the target SQL Server instance.
Information about the blocking chains observed need to be captured by using the SQL 2008 Perf
Stats custom collector.
The SQL Memory Error custom collector collects diagnostic information about the target
SQL Server instance's memory usage, which can be useful while troubleshooting SQL
out-of-memory issues.

WARNING Some of the data captured by these custom diagnostics will not be
captured when running SQLdiag from a remote machine. This is one of many
reasons why it is recommended to run SQLdiag from the machine on which the
SQL Server instance is installed.

The SQL Agent custom collector collects all the SQL Server Agent logs and the backup of the
MSDB database from the target SQL Server instance. This can result in the output folder becoming
very large, and can dramatically increase the time it takes for the SQLdiag shutdown phase, which
would be directly proportional to the size of the MSDB database. It is probably a good idea to col-
lect the relevant data from the MSDB database tables if the MSDB database is quite large.