I was recently asked following question about how to Check Advanced Server Configuration.
“How I check all the advanced configurations of the SQL Server?”
EXEC sp_configure 'Show Advanced Options', 1; GO RECONFIGURE; GO EXEC sp_configure;
Running above script will return all the advanced server configurations.
If you run above script you will find following result set.
SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration of SQL Server client computers.  SQL Server Configuration Manager performs additional configuration, such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. The SQL Server Configuration Manager also allows you to view information about failover cluster instances.
The result which you see is based on my personal computer. In your case, it may be a bit different based on the value. Let me know if you have ever found this particular query useful. I will be happy to know your thoughts in the comments section of this blog post. I am very eagerly waiting for your feedback about this subject.
Happy Coding!
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
We can also query below view to check advanced option.
SELECT * FROM sys.configurations
Thanks for the script, always used the one pinal mentioned in the article.
What i do not understand is why is the call to RECONFIGURE needed, if its just changing display behaviour? :-/
We need RECONFIGURE as it updates running value (run_value) also, without which it will not change run_value.
The other option to get updated run_value apart from RECONFIGURE is to stop and start the Server, which is not advisable.
Also some of the options will not update the run_value even after using RECONFIGURE
When you have checked these advanced configurations of the SQL Server, I guess you have to perform the same query EXEC sp_configure ‘Show Advanced Options’, 0;?
Absolutely run EXEC sp_configure ‘Show Advanced Options’, 0; to turn it off when you are finished.