In this blog post I will explain the difference between reconfigure and reconfigure with override which are used to accept sp_configure changes. This post is part of our Exam Guide 70-462 Administering SQL Server 2012
Microsoft introduced in SQL Server two ways to accept changes:
-
Reconfigure - Will accept only values that are reasonable and will error if you provide values that are not recommended.
-
Reconfigure with override - will accept any value you provide (by disabling configuration value checking
Let's show you an example:
-
Reconfigure recovery interval using value between 0 and 60 (recommended range)
use master
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
go
EXEC sp_configure 'recovery interval', 30
RECONFIGURE
Our above code execute successfully but if we change the value to 120
use master
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
go
EXEC sp_configure 'recovery interval', 120
RECONFIGURE
Then we get error:
Recovery intervals above 60 minutes not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.
so to save change and accept not recommended value we can use the code below
EXEC sp_configure 'recovery interval', 120
RECONFIGURE WITH OVERRIDE
and that will work
Hope that helps
Take care
Emil
