Hi,
One of my fellow DBA team member came to me with a problem, but let me provide descriptions of this first.
Description
************
we have a recetnly installed server ofcourse SQL Server 2008, created a maintenance plan with CHECK DATABASE INTEGRITY TASK with INCLUDE INDEXES option selected.
When we execute this maintenance plan it fails with below error:
So as the screenshot mentions that plan is failing because its not able to perform ALTER task in current SQL Instance.
Now why is it so? I am just created a Maintenance Plan with Database Integrity task and its failing? Why?
Troubleshooting
***************
I ran profiler and checked why its failing?
In Profiler I found errors like below when it was trying to execute SP_CONFIGURE 'USER OPTION',0 ; RECONFIGURE
Error: 5808, Severity: 16, State: 1
Ad hoc update to system catalogs is not supported.
Wait.. why its complaining AD HOC UPDATE TO SYSTEM CATALOGS is not supported? Why its trying to change SYSTEM CATALOGS at first place? In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables. In SQL Server 2005 and newer, the system tables are gone, replaced with the Resource Database and system views. This option is no longer supported in SQL Server 2005 and beyond, and though you can set Allow Updates to 1 with no error, as soon as you run RECONFIGURE, you will receive the error that ad hoc updates are not supported. Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.
As per Books On Line:
Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.
So i checked current setting SP_CONFIGURE and found ALLOW_UPDATE config value as 1. So i configured ALLOW UPDATE back to 0 and again ran Maintenance Plan and it executes fine.
Below is the command used for setting this option
SP_CONFIGURE 'ALLOW UPDATE',0
RECONFIGURE
Regards
Gurpreet Sethi
One of my fellow DBA team member came to me with a problem, but let me provide descriptions of this first.
Description
************
we have a recetnly installed server ofcourse SQL Server 2008, created a maintenance plan with CHECK DATABASE INTEGRITY TASK with INCLUDE INDEXES option selected.
When we execute this maintenance plan it fails with below error:
So as the screenshot mentions that plan is failing because its not able to perform ALTER task in current SQL Instance.
Now why is it so? I am just created a Maintenance Plan with Database Integrity task and its failing? Why?
Troubleshooting
***************
I ran profiler and checked why its failing?
In Profiler I found errors like below when it was trying to execute SP_CONFIGURE 'USER OPTION',0 ; RECONFIGURE
Error: 5808, Severity: 16, State: 1
Ad hoc update to system catalogs is not supported.
Wait.. why its complaining AD HOC UPDATE TO SYSTEM CATALOGS is not supported? Why its trying to change SYSTEM CATALOGS at first place? In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables. In SQL Server 2005 and newer, the system tables are gone, replaced with the Resource Database and system views. This option is no longer supported in SQL Server 2005 and beyond, and though you can set Allow Updates to 1 with no error, as soon as you run RECONFIGURE, you will receive the error that ad hoc updates are not supported. Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.
As per Books On Line:
Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.
So i checked current setting SP_CONFIGURE and found ALLOW_UPDATE config value as 1. So i configured ALLOW UPDATE back to 0 and again ran Maintenance Plan and it executes fine.
Below is the command used for setting this option
SP_CONFIGURE 'ALLOW UPDATE',0
RECONFIGURE
Regards
Gurpreet Sethi
10 comments:
Thank you! Worked like a charm for me!!!
Thank you! Worked like a charm for me!!!
http://sequelserver.blogspot.ca/2013/04/sql-server-2008-maintenance-plan-error.html
Thanks, this worked perfectly.
Never had this issue on any of our other SQL servers before, bizarre!
Thanks. It worked.
Server buddies provides Quality remote server management including troubleshooting, Server Management, Plesk Support, Server Maintenance, Server Monitoring, Server Troubleshooting and support for a large variety of customers at affordable rates. Here are option for Server Management, Server Maintenance, Server Monitoring, Server Troubleshooting, Server Optimization, Plesk Support, Linux Support, cPanel Support and Plesk Support.
.........
I also had the same problem and your approach did indeed solve it. Thanks a lot!
Really good information to show through this blog. I really appreciate you for all the valuable information that you are providing us through your blog.
Digital Marketing Training in Chennai
Digital Marketing Course in Chennai
Post a Comment