Ever wonder if there was a way to quickly and easily know which sp_configure options were dynamic, besides having to read the BOL? What? Am I the only one? OK, fine, but at least I knew of a way to get that done as well as to return the current running values without having to capture and parse the output from sp_configure.
As always, please note that this system table will be removed from a future version of SQL Server.
What is this table for?
This table contains a row for every option available that can be set by a user running sp_configure. But unlike sp_configure, this table will display all of the configuration options and values without that pesky need to enable ‘show advanced options’.
Why should you care?
If you are like me then when you are given a server to administer you are likely to make a few configuration changes, both on the initial deployment of the server as well as over the lifetime of the server. And if you are like me then you will want to track those configuration options at least nightly, so that in the event of a disaster you are able to rebuild the server and put it back exactly like it was.
Or, sometimes you just want to verify that the settings on the test server match what is currently in production. I found that having to parse out sp_configure was a pain, especially if the box was not configured to show the advanced options. In that case, running sp_configure only gives you a handful of what you are expecting.
Go ahead and see for yourself.
SELECT * FROM master.dbo.sysconfigures
You get back four columns. The first (value) returns the current running value for the configuration option that is describe in the third column (comment). The second column is just a number to identify the configuration variable and no, I don’t know why they start at 101 and go up to 16392. If you do, leave a comment, thanks. The last column will tell me if this value is a static (changes with a service bounce), a dynamic change (changes with a RECONFIGURE), a combination of static and advanced, or dynamic and advanced.
Where else is this information?
Remember how I said this system table will be removed from a future version of SQL Server? Then don’t delay in getting started with using the sys.configurations catalog view. Just run this and look at the goodness you get returned:
SELECT * FROM sys.configurations
I just love this view, it is so shiny. Do yourself a favor and start tracking these details, you never know when you might need them.
Private Detective services in Mumbai India, extra martial affairs Investigation Services, Pre matrimonial investigator in Mumbai, Post matrimonial, detective agency, Mumbai detective, Pune Detective Service, Detective in Mumbai.