Tip

Viewing configuration settings in SQL Server 2000

Barrie Sosinsky, Contributor

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]
GO
RECONFIGURE WITH OVERRIDE
GO

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

    Requires Free Membership to View

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
GO
RECONFIGURE
GO

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 first published in March 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.