Error when you try to delete a maintenance task SQL 2005
This error is elusive, there is no problem with connection! Most likely you cannot delete the task because there are records of it's execution stored in two tables that I will introduce shortly. First let me show you a table where maintenance tasks are listed. This is the same as viewing the maintenance tasks in Management Studio except doing it with T-SQL.
SELECT * FROM MSDB.DBO.SYSDTSPACKAGES90
The other table that has reference to the maintenance task is called MSDB.DBO.SYSMAINTPLAN_SUBPLANS.
Now if you run a delete with the maintenance plan ID you're trying to get rid off, you will clean out any subplans that your maintenance task has.
Task is deleted!
First of all, there are recommendations not to touch system dbs. So far this is the only way I was able to find that can get rid off rogue maintenance tasks. Until then, I will continue to use this. Always run a full backup of msdb database before performing this process. I am not responsible for your actions and use this procedure at your own risk!
1. SELECT NAME, ID FROM MSDB.DBO.SYSDTSPACKAGES90 WHERE ID = 'THE ID OF THE ROGUE MAINTENANCE TASK'
2. DELETE FROM MSDB.DBO.SYSMAINTPLAN_LOG WHERE PLAN_ID = 'THE ID YOU GOT FROM STEP 1'
3. DELETE FROM MSDB.DBO.SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = 'THE ID YOU GOT FROM STEP 1'
4. DELETE FROM MSDB.DBO.SYSDTSPACKAGES90 WHERE ID = 'THE ID YOU GOT FROM STEP 1'