Earlier sql server was having diagnoses tool.
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.
CREATE EVENT SESSION [abc]
ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)
ADD TARGET [asynchronous_file_target] (set filename=’c:\abc.xel’)
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”