SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

sp_configure OR sys.configurations? and do you back these up?


I was checking out some of our configuration options today using sys.configurations and I started to wonder how many people actually use this table, or whether most people simply use the more common system stored procedure sp_configure.

As sys.configurations is simply a table, we can use an ORDER BY clause as in

SELECT * FROM sys.configurations C
ORDER BY C.name;

Which lists the configuration options in exactly the same order as the following call to sp_configure.(depending on the value of “show advanced options” the amount of options returned may vary.)

/*Just run the
Stored proc.*/
EXEC sp_configure

However, obviously with SELECT statement we can also use a WHERE clause (or any other valid syntax)

SELECT * FROM sys.configurations C
WHERE C.is_advanced = 1
ORDER BY C.is_dynamic , C.name;

Of course it’s probably a matter of personal choice whether to write a query or just go for the system stored procedure. But one of the reasons that I like the query option is that I can see the advanced options straight away – even if I have “show advanced options” set to 0. If I use the stored procedure then I have to first set “show advanced options” to 1, then run a RECONFIGURE (as “show advanced options” is a dynamic option). Next the system stored procedure is run and finally “show advanced options” is set back to 0, followed by another RECONFIGURE. Note also that the code below ends by setting “show advanced options” to 0, when it may have already been at 1 in the first place.

so the full TSQL actually looks like

EXEC sp_configure 'show advanced options' , 1;
EXEC sp_configure;
EXEC sp_configure 'show advanced options' , 0;

instead of

SELECT * FROM sys.configurations C

which seems like less typing and also simply just leaves any setting for “show advanced options” alone.

But there is another good use for the table option, rather than the stored procedure. And that is to pull all of your setting out and back them up. Maybe pushing them to a text file on the server using Powershell or SSIS.
You can format the query anyway you like (perhaps in the form of runnable SQL statements) and then in a DR situation you have a full list of all your configuration setting without having to restore master to get them.

anyway, have a nice day.




Posted by Anonymous on 21 October 2011

Pingback from  Dew Drop – October 21, 2011 | Alvin Ashcraft's Morning Dew

Posted by Steve Jones on 21 October 2011

Interesting idea here. I personally store the sp_configure output for servers and then compare it with the previous day's version, alerting admins if something changes. Good for DR as well since I have the settings when I restore my admin db.

I might change to sys.configurations, however, since you have to remember to show advanced settings.

Posted by Perry Whittle on 24 October 2011

for querying the values i use the table(s). For those of us still supporting SQL Server 2000 systems, this object has had a name change between SQL Server versions (sysconfigures vs sys.configurations), so just need to be aware of that.



Posted by Anonymous on 1 November 2011

Pingback from  Sp_configure OR  sys.configurations? and do... | SQL Server | Syngu

Posted by sami.sqldba on 30 November 2011

I really apprecaite your thinking its really a great peice of knowleadage thanks again.

Leave a Comment

Please register or log in to leave a comment.