Denali – Day 24: True Black Box Recorder – sp_server_diagnostics

Denali – Day 24: True Black Box Recorder – sp_server_diagnostics


Earlier sql server was having diagnoses tool.

Default trace.


And other tools but could not got the one which we can all it as True Black Box Recorder
by “Bob Dorr – Principal SQL Server Escalation Engineer“” he called sp_server_diagnostics” as a true black box recorder for sql server.

“sp_server_diagnostics” is a new stored procedure introduced in Denali which captures the data of sql server activity current or with interval of 5+seconds (minimum 5 seconds).as it takes that much of time to gather required information. Default is 0, means just run once and produce the result.



sp_server_diagnostics [@repeat_interval =] ‘repeat_interval_in_seconds’


It reserved its own memory for execution so at any circumstances it will definitely run and produce the output like a Black Box which provides the information about the reason for the issue.



Or can save the output into a sql table.


But general recommendation is to keep it outside sql server so that for any issue on sql server the Black Box will be safe and we can diagnose the issue.




           ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)

           ADD TARGET [asynchronous_file_target] (set filename=’c:\abc.xel’)



      ON SERVER STATE = start


The output of sp_server_diagnostics is


  • System – Information related system like CPU, and other system specific.
  • Resource – Information related to resource like memory.
  • Query Processing – information related to Query.
  • IO Subsystem – information related to IO.
  • Events – information related to stored procedure on the errors , events, security, and connectivity.


So using this we could diagnose the issue on sql server.


We can change the location of the log file using “ALTER SERVER CONFIGURATION”


This entry was posted in Denali, Troubleshooting and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.