Error al querer eliminar un job de SQL Server 2005

Al intentar eliminar un Job del SQL Server 2005 puede aparecer el siguiente mensaje de error

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)

nos indica que no se puede eliminar un job por problemas con los planes de mantenimiento.

Para poder resolver este problema y poder eliminar el Job les dejo el siguiente Script

declare @job_name varchar(500)

set @job_name = N’tujob’ ;

 

USE [msdb]

delete sysmaintplan_log

FROM sysmaintplan_subplans AS subplan

INNER JOIN

sysjobs_view AS jobs ON subplan.job_id = jobs.job_id INNER JOIN

sysmaintplan_log ON subplan.subplan_id = sysmaintplan_log.subplan_id

WHERE (jobs.name = @job_name)

 

delete sysmaintplan_subplans

FROM sysmaintplan_subplans AS subplan INNER JOIN

sysjobs_view AS jobs ON subplan.job_id = jobs.job_id

WHERE (jobs.name = @job_name)

 

delete

from msdb.dbo.sysjobs_view where name = @job_name