Not able to delete SQL Server Maintenance Plan and/or SQL Server Agent jobs

You may get below error while trying to delete MP after server name change: -

TITLE: Microsoft SQL Server Management Studio

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

ADDITIONAL INFORMATION:

An error was encountered when trying to remove the package “Maintenance Plans\UserDB_Tlog” from SQL Server. 

and/or

You may get below error while trying to delete SQL Server agent job: -

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job ‘UserDB_Tlog.Subplan_3′.  (Microsoft.SqlServer.Smo)

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 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.

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.4060&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

Possible cause of this issue: -

Mainly you will face issue if your SQL Server host name is changed.

Solution: -

In order to delete the maintenance plan and/or SQL Server agents jobs, we have to manually delete the entries from system tables in msdb database. Below are the queries you need to fire in msdb database in order to get this done.

select * from sysjobs -- to find the correct jobid

select * from sysjobschedules where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysjobschedules] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select plan_id from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select * from dbo.sysmaintplan_log where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value from above sql

delete from [dbo].[sysmaintplan_log] where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value frm above sql

select * from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from sysjobs where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

Now connect to integration services on this server and manually delete the package under msdb