Ad hoc update to system catalogs is not supported

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.

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)

Solution 2: (Change allow_updates parameter using sp_configure)

After executing the above script, you can execute sp_configure without “WITH OVERRIDE”. I prefer always using “WITH OVERRIDE”. Because Microsoft announced that it will remove “allow updates” in a future version of SQL Server.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *