Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



sp_configure - RECONFIGURE or RECONFIGURE WITH OVERRIDE
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-06-19









SQL Server Configuration options are available to manage via SQL Server Management Studio Application (SSMS) or sp_configure stored procedure.

sp_configure will provide all the available configuration options, while SSMS provides a subset that is comprised of the commonly used configuration options.

When using sp_configure, a DBA has a couple different options for implementing the desired change. The first is RECONFIGURE and the second is
RECONFIGURE WITH OVERRIDE.

Note: Modifying any of these global configuration options/settings for any SQL Server system needs to be completed by an experienced administrator who has taken the necessary time to research the pros and cons of any change.



Example: Modifying a SQL Server Configuration Option:


recovery interval:
- Upper time limit a recovering a database should complete.
- MSSQL Engine uses this value to determine the frequency of automatic checkpoints issued against a database.

Default Setting:
recovery interval (min) 0(min) 32767(max) 0(config_value) 0(run_value)



Modify using Reconfigure:

USE MASTER
GO
EXEC sp_configure 'recovery interval (min)', 90;
GO
RECONFIGURE
GO

Output:

Configuration option 'recovery interval (min)' changed from 0 to 90. Run the RECONFIGURE statement to install.
Msg 5807, Level 16, State 1, Line 9
Recovery intervals above 60 minutes not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.

Note: Using the value "90" for demonstration purposes only. This is not a best practice. See the link below for recommended Configuration Options Values.

Setting:

recovery interval (min) 0(min) 32767(max) 90(config_value) 0(run_value)



Modify using Reconfigure With Override:

EXEC sp_configure 'recovery interval (min)', 90;
GO
RECONFIGURE WITH OVERRIDE
GO

Output:

Configuration option 'recovery interval (min)' changed from 90 to 90. Run the RECONFIGURE statement to install.

Setting:

recovery interval (min) 0(min) 32767(max) 90(config_value) 90(run_value)



You can see more details on Configuration Options by issuing the below query:

Select * from sys.configurations


Resources:

Server Configuration Options (SQL Server)