<b>案例環境:</b>
資料庫版本: microsoft sql server 2005 (microsoft sql server 2005 - 9.00.5000.00 (x64) )
<b>案例介紹:</b>
對一個資料庫執行個體做清理工作時,發現有一個很久之前禁用的資料庫維護作業,于是遂删除該作業,但是删除該作業時,遇到如下錯誤:
腳本删除操作:
圖形界面操作:
<b></b>
<b>案例分析:</b>
<b></b>
從錯誤資訊我們可以看出是删除某個系統表中記錄時,由于外鍵限制關系,
導緻删除失敗。最後導緻存儲過程msdb.dbo.sp_delete_job執行失敗。我想徹底弄清楚删除失敗的具體原因,于是可以從提示資訊的系統表
dbo.sysmaintplan_subplans開始,如下所示,
可以看到系統表dbo.sysmaintplan_subplans中
的job_id字段引用了msdb.dbo.sysjobs中的job_id字段,那麼可以肯定是在删除msdb.dbo.sysjobs表中對應記錄
時,沒有先删除dbo.sysmaintplan_subplans中的記錄。這樣推測也跟報錯資訊吻合。
那麼接下來我們研究一下msdb資料庫的存儲過程[dbo].[sp_delete_job] 如下所示:
從上面sql腳本中可以看到在删除msdb.dbo.sysjobsteps之前,該存儲過程執行了msdb.dbo.sp_delete_job_references
而msdb.dbo.sp_delete_job_references這個存儲過程又接着調用了存儲過程sp_maintplan_delete_subplan,
也就是說最終在此存儲過程sp_maintplan_delete_subplan中删除msdb.dbo.sysmaintplan_subplans 表中的記錄。 過程梳理清楚了,那麼逆向推導看看具體原因
如下所示,删除msdb.dbo.sysmaintplan_subplans中對應記錄語句如下
此時要看參數@subplan_id的取值,它從msdb.dbo.sp_delete_job_references中傳入,如下所示
alter procedure [dbo].[sp_maintplan_delete_subplan]
@subplan_id uniqueidentifier,
@delete_jobs bit = 1
as
…………………………………………………………………
在[dbo].[sp_delete_job_references]中,它的值來自于 @maintplan_subplan_id變量,最終來自于sysmaintplan_subplans系統表
我通過dac登入資料庫(sysmaintplan_subplans是内部對象,此對象在dac下才可以通路),查詢如下所示,你會發現無記錄,也就是說@maintplan_subplan_id為null值,導緻後面執行删除msdb.dbo.sysmaintplan_subplans表中記錄時,沒有真正的删除記錄。
最後發現導緻查詢無記錄的原因在于查詢條件sjv.master_server = 1
sysjob_view視圖代碼如下所示:
繼續往下扒,視圖dbo.sysoriginatingservers_view代碼如下所示,
原來master_server的值是預設的。因為表dbo.sysoriginatingservers無記錄。至此,可以看出,這應該是sql server 2005的一個bug來的。
解決方案:
手工删除系統表msdb.dbo.sysmaintplan_subplans中的記錄,然後删除該作業。問題搞定。