Problem solve Get help with specific problems with your technologies, process and projects.

Viewing configuration settings in SQL Server 2000

See how to view configuration settings in Microsoft SQL Server and learn what to do if changes need to be made.

To view SQL Server's configuration settings in version 2000, 7 and 6.5, you can issue the sp_configure in the Query...

Analyzer. That command returns a table with the setting parameter name, its minimum and maximum values for that setting, as well as its config_value and run_value. The config_value is the specified value of the setting, although not necessarily the true current value – which is the value you see in the last column called the run_value. At startup, SQL Server's config_value should be the same as the run_value.

You can change the values of settings using the sp_configure command syntax or in the Enterprise Manager, but you should be familiar with the effects of these changes before making them. To check the default value of the settings, check with the SQL Server Book Online. Be aware that some of the settings you alter will not change until you either physically restart SQL Server, or if you have specified the RECONFIGURE WITH OVERRIDE option, if that setting has one.

The sp_configure command syntax is:
sp_configure ['setting name'], [setting value]

You must enclose the name in single or double quotes depending upon how the Query Analyzer is configured. The numeric value takes no quotation marks. The RECONFIGURE command is the default, while the RECONFIGURE WITH OVERRIDE will force the change. Most but not all settings change without a restart of the SQL Server service. Enterprise Manager executes setting changes with RECONFIGURE WITH OVERRIDE set as the option.

SQL Server prevents administrators from messing with some of the advanced settings by requiring that you turn the sp_configure command on. Run the following code:
sp_configure 'show advanced options', 1

Once you do this you will see which settings are advanced and can now be changed in the table.

Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.

This was last published in March 2005

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.