• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2270
  • Last Modified:

Error when trying to remove old jobs and maintenance plans

I am trying to delete some jobs from Management Studio that are not in use anymore and now they are just in the way.  These were maintenance plans that were then scheduled as jobs.  They were all created by the former dba and now I have created better versions of the same and want to delete the old 3 or 4 jobs off as well as the maintenance packages that are tied to them.  When I try to delete each job I get a similar message to the one below, but this one below is the exact message I get when I try to delete one of them:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Job 'Weekly_Full_System_Backup.Subplan'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


Okay so I'm lost on that one but also when I try and delete the maintenance plan I receive this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'dburgess'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

dburgess was the login of the dba before me and even though the IT Director changed the owner of the maintenance plans over to my login and I can see the proof of that when I go into the properties of the maintenance plan it obviously still has some association with the old dba.  His login has been removed everywhere without any errors but now I can't get rid of his old work that we don't need.  

Please help!
0
jacobymatt
Asked:
jacobymatt
2 Solutions
 
SQL_SERVER_DBACommented:
TRY DELETING THE JOBS ASSOCIATED WITH IT FIRST
0
 
jacobymattAuthor Commented:
The first error that I pasted is the error I get when trying to delete the job.  I'll paste it again:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
0
 
jacobymattAuthor Commented:
anyone???
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jacobymattAuthor Commented:
really need some help on this!  Has anyone seen this error before when trying to delete their job and/or maintenance plan?
0
 
jacobymattAuthor Commented:
What's going on?  Do I need to increase the points, this doesn't seem like it would be that difficult?
0
 
jacobymattAuthor Commented:
I'll take that as a yes.  Help please!
0
 
jacobymattAuthor Commented:
OK....I thought maybe I would try going to the manage schedules option off of Jobs and deleting the job/schedule from within there but I still get this error:

Drop failed for JobSchedule 'Daily_Full_HTQ_Backup'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_schedule_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'schedule_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
-----------------------

It seems like no matter how I try to delete these old jobs, SQL is not deleting the related child records in the right order.  I have about 5 jobs I need to delete that all give me this same error.  Surely someone else has had this problem and knows the workaround.  Please help!  Increasing points again!!!
0
 
SQL_SERVER_DBACommented:
delete the job that associated with it first.
0
 
jacobymattAuthor Commented:
Seriously are you even reading all that I've written above?  That is the error I get when I try to delete the job!  The problem is the relationship between the subplan and the schedule (sysmaintplan_subplans to sysschedules), somewhere there are orphaned records (maybe?) but I don't know how to solve it.  
0
 
Don_SmithCommented:
I was getting this exact error, and I found a solution at
http://www.sqlservercentral.com/Forums/Topic388432-146-1.aspx

To summarize briefly:
SELECT * FROM sysdtspackages90 (to get the Plan_ID of the Bad Plan)
DELETE FROM sysmaintplan_subplans WHERE plan_id = BadPlanID
DELETE FROM sysmaintplan_log WHERE plan_id = BadPlanID
DELETE FROM sysdtspackages90 WHERE plan_id = BadPlanID

After doing this, the plan goes away by itself, and the associated job can be deleted w/o error.
This mess seemed to be caused by using SSIS to import a maintenance plan from one instance to another - seemed like a good idea at the time, but I won't do THAT again!

Hope this helps!
0
 
Jim P.Commented:
I got it as below, but Don_Smith substatially had it.
SELECT * FROM sysdtspackages90 
 
DELETE FROM sysmaintplan_subplans WHERE plan_id = BadPlanID
DELETE FROM sysmaintplan_log WHERE plan_id = BadPlanID
DELETE FROM sysdtspackages90 WHERE id = BadPlanID

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now