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]
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 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

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close