SQL SERVER – Check Advanced Server Configuration

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;
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 - Check Advanced Server Configuration sp_configure-500x492

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)

SQL Scripts, SQL Server, SQL Server Configuration, SQL Stored Procedure
Previous Post
SQLAuthority News – 2 Sessions at TechInsight 2010 – June 29 – July 1, 2010
Next Post
SQL Tips – 5 SQL Server Best Practices

Related Posts

6 Comments. Leave new

  • We can also query below view to check advanced option.

    SELECT * FROM sys.configurations

  • 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.


Leave a Reply