http://www.sqlservercentral.com/blogs/vinaythakur/2012/05/24/denali-day-24-true-black-box-recorder-sp_server_diagnostics/

Printed 2014/11/23 04:13AM

Denali – Day 24: True Black Box Recorder – sp_server_diagnostics

2012/05/24

Denali – Day 24: True Black Box Recorder – sp_server_diagnostics

 

Earlier sql server was having diagnoses tool.

Default trace.

SQLDiag

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.

 

Syntax:

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.

GO

 

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.

 

CREATE EVENT SESSION [abc]

ON SERVER

           ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)

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

GO

ALTER EVENT SESSION [abc]

      ON SERVER STATE = start

 

The output of sp_server_diagnostics is

 

 

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

 

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

 

http://blogs.msdn.com/b/psssql/archive/2012/03/08/sql-server-2012-true-black-box-recorder.aspx

 

http://msdn.microsoft.com/en-us/library/ff878233(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ee210585.aspx


 



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.