Sunday, 7 June 2009

SQL undeletable jobs

It came to my attention a few weeks ago while where implementing an enterprise automation that jobs create from T-SQL script related to maintenance plans sometimes couldn't be deleted, this also applies to some rare times when maintenance plan deleted but the job didn't.

The result is a job that can't be deleted because its is linked to an entry in MSDB where they are still held.

When the maintenance plan had ether been deleted or was not viewable as it had been created by T-SQL, sadly T-SQL doesn't create the XML file needed for the maintenance plan to be seen from SQL management studio.

As a result you can not delete the job without first deleting the links to it in the MSDB these can be found in the following three tables.

sysmaintplan_subplans, sysmaintplan_plans, sysmaintplan_log

these three tables have to be cleaned up before you can delete the job as it is listed in one or more of them, luck for us there is a common id column call PLAN_ID

So first we need to find the plan_id of our job, if you've been using descriptions on your maintenance job this will be easy if not then you might want to open them up and add descriptions as other wise you'll have a lot of plans and no way to identify them.

Querying the three tables will let us see how many it exist in, this takes but a few seconds.

use msdb
select * from sysmaintplan_subplans

select * from sysmaintplan_plans

select * from sysmaintplan_log


with the output we where able to identify the plan_id as it was the only one without a description, and from there could delete the plan_id from the tables like so.


DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID='9AA4A25B-D4B3-466D-8C9E-20A00722F2CA'

DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = '9AA4A25B-D4B3-466D-8C9E-20A00722F2CA'

DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = '9AA4A25B-D4B3-466D-8C9E-20A00722F2CA'


after this was done we where then able to delete the job as there was no relation to it in the MSDB any longer.

No comments: