You may receive this error when you want to chage an instance level parameter by using sp_configure in SQL Server. I would recommend reading the article below to get detailed information about the instance level configurations you can do with sp_configure.
“sp_configure (Server-Level Configurations in SQL Server)“
For example, when we try to change the recovery interval as follows, we will see the below error.
|
1 2 3 4 5 6 7 |
USE [master] GO EXEC sp_configure 'show advanced options',1; RECONFIGURE GO EXEC sp_configure 'recovery interval', '0' --in minutes RECONFIGURE |
Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 4
Ad hoc update to system catalogs is not supported.
Configuration option ‘recovery interval (min)’ changed from 0 to 0. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 7
Ad hoc update to system catalogs is not supported.
SOLUTION:
Whe have two options to solve this problem:
Solution 1 : (Use RECONFIGURE WITH OVERRIDE on the script)
|
1 2 3 4 5 6 7 |
USE [master] GO EXEC sp_configure 'show advanced options',1; RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'recovery interval', '0' --in minutes RECONFIGURE WITH OVERRIDE |
Solution 2: (Change allow_updates parameter using sp_configure)
|
1 2 3 4 |
USE [master] GO EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE |
