Programmatic detection of configuration options

A quickie… this morning I've been writing a bunch of T-SQL code (yes, occasionally I'm allowed to do that :-) and I needed to programmatically check whether backup compression is enabled on a SQL Server 2008 instance. I futzed about for a few minutes with sp_configure and I was starting to think of a temp table plus INSERT/EXEC when I thought there has to be a better way.

I turned to my trusted friend Books Online (I'm constantly amazed at some of the questions people ask in forums and on Twitter than can easily be answered by looking in Books Online…) to help out and of course was reminded straight away of the companion view to sp_configure: sys.configurations (see Books Online here).

Here's a quick example:

IF (SELECT [value_in_use] FROM sys.configurations
WHERE [name] = 'backup compression default') = 1
SET @BackupCompress = 1;

It's always amazing how much you forget and how much there is to know – enjoy!

4 thoughts on “Programmatic detection of configuration options

  1. Yes, I only use sys.configurations unless I have to change something. Not only can you access the settings programmatically as you discovered, but you don’t have to turn on the ‘Show Advanced Options’ option to see everything on a new system you’re checking out. Plus it has two very useful extra columns. It has a boolean to indicate whether the option is advanced or not, and also one to indicate whether the option is dynamic or not.

  2. Yes, I find this view very useful to use in shared administration SQL Servers, where p.ex. I need to run some SP that requires xp_cmdshell or OLE Automation.

    I read the configuration value at startup on my SP, set a flag, and then put everything I change back to the way it was as not to mess with other code running on the same instance. This is especially useful for backups.


    Ivo Pereira
    IT Consultant

  3. SET @BackupCompress = (SELECT [value_in_use] FROM sys.configurations
    WHERE [name] = ‘backup compression default’)

    is better ;)

  4. But from a pure programming perspective, I’d never use a construct like that because it assumes that value_in_use will always remaain 1 or 0 in future releases. IMHO it’s also less readable.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.