Link to home
Start Free TrialLog in
Avatar of jacobymatt
jacobymatt

asked on

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!
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

TRY DELETING THE JOBS ASSOCIATED WITH IT FIRST
Avatar of jacobymatt
jacobymatt

ASKER

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)
anyone???
really need some help on this!  Has anyone seen this error before when trying to delete their job and/or maintenance plan?
What's going on?  Do I need to increase the points, this doesn't seem like it would be that difficult?
I'll take that as a yes.  Help please!
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!!!
delete the job that associated with it first.
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.  
ASKER CERTIFIED SOLUTION
Avatar of Don_Smith
Don_Smith

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial