Certain SQL Server Agent jobs failing due to Allow Updates option

Written By: Danny Rose

from April 25, 2013

I recently started working on a case where backups were not running although they were scheduled and enabled to run via SQL Server Agent 2008. They had previously been run without issue for at least a few months, but suddenly stopped.  After some testing, I realized even new jobs would not even run on demand for certain processes such as backups and the Check Database Integrity Task.  Others, Reorganize Index and Update Statistic Tasks, ran successfully and without issue.

After a little searching, I found that the ‘Allow updates’ field from sp_configure had somehow been updated to 1, which basically enables users to update system tables through ad-hoc updates.  By default, this value is set to 0, which disables it, to prevent data loss or other potential issues – it is strongly recommended to keep this option set to disabled.

You can view this setting by looking at the config_value field by executing the following in SQL Management Studio: sp_configure

If needed, this option can be updated to disabled by running the following:

Disable:  sp_configure “allow updates”, 0