Finding non-default configuration settings in SQL Server

By | In Blog, SQL Server | August 25th, 2011

In our Remote DBA practice, we frequently perform comprehensive system reviews for our customers on their database services. Among the things we always check for are non-default settings for the database software. We want to validate that any non-default setting is set that way for a good reason, and that any setting that is default really should be that way. In Oracle, this is easy.  The gv$parameter view has a column, ISDEFAULT, that allows a simple SQL query to show which settings are set to non-default values. It’s not so easy in SQL Server.  There is a view, master.sys.configurations, but it doesn’t have a way to tell if the setting is default or modified or anything. That’s why I was pleased to find that Michael Campbell came up with a good solution that hard codes known default values into a script that works for SQL Server 2008 and up. The style of insert used in the above script doesn’t work for SQL Server versions lower than 2008, so I made slight changes to allow it to work on 2005 and lower.  Here it is, with full attribution to Michael Campbell for developing the underlying script and technique. I can’t guarantee all the default values are valid for earlier versions, but the script runs and shows results for SQL Server 2005.

— Server Configuration (find any non-standard settings) — for SQL Server 2008.


Contact Us

Leave a Reply

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