SQL Server 2008 维护计划执行失败一例

前几天碰到一个问题, SQL Server 2008 SP2 上的一个检查数据库完整性的维护计划失败了, 这个维护计划没有过任何的修改.
先查看一下执行这个维护计划的job的历史, 有类似以下的报错:
 
Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete
End Progress  DTExec: The package execution returned DTSER_FAILURE (1).
Started:  10:14:30 AM  Finished : 10:14:31 AM  Elapsed:  1.185 seconds. 
The package execution failed.  The step failed.
 
从这个报错里看不出什么东西. 只知道这个job是10:14:30 AM 这个时候开始的, 执行了1.185秒就报错了.
 
然后再查看一下维护计划的历史, 只有如下的一条报错:
 
Alter failed for Server 'server_name\\instance_name'
 
看起来有点莫名, 从维护计划生成的语句来看, 也就是dbcc checkdb之类, 为什么会有alter server的操作?
 
再查看一下errorlog, 发现在维护计划报错的那一时刻, 有如下一条报错:
 
Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
 
看不出个所以然,  还是打开profiler看一下吧. 于是用profiler抓到了以下的语句:
 
EXEC sys.sp_configure N'user options', 0 RECONFIGURE
go
 
EXECUTE msdb..sp_maintplan_update_log '3E94A9A2-B4DD-4BA8-88E0-065DD7F1E90C'
,'Check Database Integrity Task (server_name)','Check Database integrity on Local server connection'
,'Databases: All databases','Include indexes','','server_name','0'
,'2012-01-17T10:40:41','2012-01-17T10:40:42',0,'Alter failed for Server ''server_name''. ',''
go
 
看来执行过sys.sp_configure之后, 就报错了, 干脆直接执行一下
EXEC sys.sp_configure N'user options', 0 RECONFIGURE
 
果不其然, 报错了:
 
Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.
 
从报错信息看, 想起了sp_configure中有一个allow updates的选项, 是不是和这个有关系, 查看一下
 
exec sys.sp_configure 'allow updates'
go
 
返回的结果:
 
name  minimum maximum config_value run_value
allow updates  0                1
1  1
 
看来问题就是在这里.
 
在SQL Server 2005及以后, 就不允许直接更新系统表了, 所以即便这个allow updates的设置是1, 并且在设置的时候没有报错, 但是要执行reconfigure使更改生效, 还是会报错.
而执行维护计划的过程中,就执行了reconfigure, 所以导致维护计划的执行报错.
 
那么解决的办法很简单, 执行个a以下语句把allow updates 改成0就可以了.
 
exec sp_configure 'allow updates', 0
 
同理, 如果执行了以下语句修改recovery internal, 再执行维护计划也是会报错.
 
EXEC sp_configure 'Recovery interval', 61
go


摘自 lucky7_2000的专栏