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