March 10, 2018 at 3:55 pm
Comments posted to this topic are about the item FCI Diagnostics Log
March 12, 2018 at 1:54 am
Good question to start the week on, thanks Steve
Really got the grey matter moving
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
March 12, 2018 at 9:22 am
Hmm, on the Failover Policy for Failover Cluster Instances documentation, I see the following:
During SQL Server startup, the WSFC service uses the SQL Server Database Engine resource DLL to create a new connection to on a separate thread that is used exclusively for monitoring the health status. This ensures that there the SQL instance has the required resources to report its health status while under load. Using this dedicated connection, SQL Server runs the sp_server_diagnostics (Transact-SQL) system stored procedure in repeat mode to periodically report the health status of the SQL Server components to the resource DLL.
To me, this seems to say that it is run automatically by SQL and there is no need to enable it. So as it pertains to this question, you need to enable the Logging portion of it?
March 12, 2018 at 11:06 am
I think the right answer is # 1. At my instance of MS SQL Server Express 2014 I never run ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON.
When I run this code:
USE master;
GO
EXEC sp_server_diagnostics;
I got the result described in the Docs sp_server_diagnostics (Transact-SQL) in the Result Sets paragraph.
The ALTER SERVER CONFIGURATION (Transact-SQL) states that the DIAGNOSTICS LOG ON parameter is default.
Another explanation can be found in the Docs Failover Policy for Failover Cluster Instances or in the MSDN blog SQL Server 2012 - True Black Box Recorder.
Five years ago it was also discussed in the forum, see the post "sp_server_diagnostics process running all the time".
I went through several articles that describe how to use sp_server_diagnostics such as Capture Diagnostic Data and Health Information in SQL Server 2012 using sp_server_diagnostics System Stored Procedure
Nowhere is it stated that you must first use ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON.
Anyway thanks for this interesting question.
March 12, 2018 at 11:46 am
The question is "What code starts the logging process?", not what has to be done to execute the stored procedure. I can find two links that state exactly what needs to be done for logging the process. Same as what is listed as the answer.
Sue
March 12, 2018 at 4:30 pm
Sue_H - Monday, March 12, 2018 11:46 AMThe question is "What code starts the logging process?", not what has to be done to execute the stored procedure. I can find two links that state exactly what needs to be done for logging the process. Same as what is listed as the answer.
Sue
You're right, but if you want to capture data from sp_server_diagnostics, you don't need to change the default server settings with the command ALTER SERVER CONFIGURATION.😉
March 13, 2018 at 8:09 am
david.gugg - Monday, March 12, 2018 9:22 AMHmm, on the Failover Policy for Failover Cluster Instances documentation, I see the following:During SQL Server startup, the WSFC service uses the SQL Server Database Engine resource DLL to create a new connection to on a separate thread that is used exclusively for monitoring the health status. This ensures that there the SQL instance has the required resources to report its health status while under load. Using this dedicated connection, SQL Server runs the sp_server_diagnostics (Transact-SQL) system stored procedure in repeat mode to periodically report the health status of the SQL Server components to the resource DLL.
To me, this seems to say that it is run automatically by SQL and there is no need to enable it. So as it pertains to this question, you need to enable the Logging portion of it?
The question isn't about configuration. This can be turned off. The question is on enabling this if it's needed.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply