Error when you try to delete a maintenance task SQL 2005

Have you ever had a problem deleting a maintenance task in SQL 2005? Today I am going to show you how to delete a maintenance task due to the following error:



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

Now that you know ID name of the maintenance task you're trying to delete we can review which tables hold the deletion.

If you try to delete with T-SQL you will get the following error:

It means there are records in MSDB.DBO.SYSMAINTPLAN_LOG table that are preventing you from deleting the maintenance task.

In my case, there are four records in the table that belong to this maintenance task.

Now that you run a delete statement using the maintenance task ID, you will clean up first of the two references.

The other table that has reference to the maintenance task is called MSDB.DBO.SYSMAINTPLAN_SUBPLANS.

This table shows how many subplans there are in your maintenance plan. In my case it's one.

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.

Now we will run the original select statement against SYSDTSPACKAGES90 table to make sure the maintenance task is still there.

Now you can run a delete statement against the original table or delete the maintenance task in management studio and it will be able to delete.



Task is deleted!

Summary:

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'

Enjoy!

Comments

Post a Comment

Popular posts from this blog

Vista Vulnerability Report mentions Ubuntu 6.06 LTS

Update: I posted this on MIXX since DIGG was taken care of by someone already, except that that user didn't really write a good preview for it so it got buried. This report is generating some buzz though. I emailed this report to Mark Shuttleworth of Ubuntu fame and little did I know, HE ANSWERED!!!! Whoever answered me on that side of the e-mail pipe liked the report and was wondering if it was official and whether he could quote from it. It definitely is official and I forwarded him the e-mail with this report, he said he will pass it down internally. That made my day, it's amazing that a man of his statute, that's if it was him, answers e-mails from regular people like me. That's what I call a real genuine pioneer! Thank you Mark and all of the Ubuntu Staff. Read
Read more

Running CockroachDB with Docker Compose and Minio, Part 2

This is my second post on creating a multi-service architecture with docker-compose. This is meant to be a learning exercise and typically docker-compose is used to set up a local development environment rather than a production-ready set up. I regularly, find myself building these environments to reproduce customer bugs. For a production-specific application, refer to your platform vendor documentation. At some later time, I will cover Kubernetes deployments that can be used as a stepping stone for a real-world application. Until then, let's focus on the task at hand. We're building a microservice architecture with CockroachDB writing changes in real-time to an S3 bucket in JSON format. S3 bucket is served by a service called Minio. It can act like an S3 appliance on premise or serve as a local gateway to your cloud storage. Let's dig in:
Read more

Doing "print screen" on a Mac is a pain in the ass

I am catching up with blogging today and I need to take a screenshot of my screen. I have a Macbook Pro with Ubuntu in a virtual machine. There is no printscreen button and a quick search returned "Apple + Shift + 3". Mac fanboys think that's easy and better than PC? So here's a problem. If you want to take a print screen in VM, that key combination doesn't work. What do I do besides opening the screenshot application and doing it half-assed way? I am sure you can edit key bindings but that should not be that way. If you know of any other ways, let me know.
Read more