Interview Question of the Week #061 – How to Retrieve SQL Server Configuration?

Interview Question of the Week #061 - How to Retrieve SQL Server Configuration? configuration I have observed this question in the interview question few days ago. I had a fun time to see candidates failing to answer this question. The most common answer candidate gave us was that they will right click on the server and see the properties. Well, that option will not give all the details which actual question intended. Let us see the question about SQL Server Configuration:

Question: What is the best way to see all the options set for your SQL Server programmatically?

Answer:

Method 1: Using sp_configure

Run following script and it will provide all information about the SQL Server. SQL Server provides in detail information if Advanced Options are turned on.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure
GO
EXEC sp_configure 'show advanced options', 0
GO

To change any configuration run following script using values from the NAME column of the following result set. E.g. To change max server memory run following script.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory', 2048
GO
RECONFIGURE
GO

Method 2: Using sys.configurations

You can run following script and it will display almost the same information as above query.

SELECT *
FROM sys.configurations</pre>
<pre>

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
Interview Question of the Week #060 – What is the Difference Between EXCEPT Keyword and NOT IN?
Next Post
Interview Question of the Week #062 – How to Find Table Without Clustered Index (Heap)?

Related Posts

4 Comments. Leave new

  • Farhat ullah
    March 7, 2016 4:13 pm

    My specific task Run Through Windows Scheduler at Night Time(configured by Client).Task scheduler run our exe , then this exe create connection with database server and executes round about 50 procedures one by one.During executing, given below error encounter.
    SQLSTATE =08S02
    Microsoft SQL SERVER Native Client 10.0
    SMux PROVIDER: Physical CONNECTION is NOT usable[xFFFFFFFF]

    Further more exe is placed on client side and database is placed on Server side.
    Please provide a suitable solution for this error.

    Reply
  • From where u got this ‘resource timeout’ configuration?! Checked in SQL 2014, it`s not there even with the Advanced Options enabled.

    Reply
    • Thanks for your note. Yes, you are correct. It was for illustration purpose but I need to be more careful with that. I replaced it with correct example. Thank you!

      Reply

Leave a Reply

Menu