Problem

SQL Server has a broken reference somewhere preventing you from deleting a maintenance job, presumably a job that you created as a maintenance plan.

OR (worse yet SQL claims that it isn’t “aware” of this job)

Solution

So the ultimate solution to this problem is to delete the job and all of its references/dependencies/children using a stored procedure (don’t worry, I provided the stored procedure below).

Step 1

Backup your msdb database.

Step 2

Select the sysjobs from the msdb database and copy the job_id of the plan in question. Match up the name of the job with the item you can’t delete from the SQL Server Agent’s jobs

SELECT * FROM msdb.dbo.sysjobs

Step 3

Review the results, run a few queries to see what you will be deleting. Now we don’t want to leave any orphaned items in the steps and schedule tables so run the following command to view any children of your sysjobs. You can skip this step but it give you an idea about what object you’ll have to delete from. Note that some of the tables you will need to delete items from are shown below.



SELECT * FROM dbo.sysjobsteps

WHERE job_id = ’5BED61A8-7E60-465C-AB4F-0FFFA86DB6A2′ /*READ THE JOB STEPS TO DELETE*/

SELECT * FROM dbo.sysjobschedules

WHERE job_id = ’5BED61A8-7E60-465C-AB4F-0FFFA86DB6A2′ /*READ THE SCHEDULES FOR THE JOB*/

Step 4

Now we can create a stored procedure to do the deleting for us (*** DON’T FORGET TO BACKUP YOUR MSDB DATABASE ***). First step is to create the stored procedure I wrote below by pasting it into a query window. You can create the stored procedure on any database, just remember to delete it when you’re done.

ALTER procedure sp_ForceJobDelete @JOB_ID CHAR(36)

AS

DELETE FROM msdb.dbo.sysjobactivity

WHERE job_id IN(

SELECT sysjobactivity.job_id FROM msdb.dbo.sysjobactivity

JOIN msdb.dbo.sysjobs

ON sysjobs.job_id = sysjobactivity.job_id

WHERE sysjobs.job_id = @JOB_ID )

DELETE FROM msdb.dbo.sysjobhistory

WHERE job_id IN(

SELECT sysjobhistory.job_id FROM msdb.dbo.sysjobhistory

JOIN msdb.dbo.sysjobs

ON sysjobs.job_id = sysjobhistory.job_id

WHERE sysjobs.job_id = @JOB_ID )

DELETE FROM msdb.dbo.sysjobschedules

WHERE job_id IN(

SELECT sysjobschedules.job_id FROM msdb.dbo.sysjobschedules

JOIN msdb.dbo.sysjobs

ON sysjobs.job_id = sysjobschedules.job_id

WHERE sysjobs.job_id = @JOB_ID )

DELETE FROM msdb.dbo.sysjobsteps

WHERE job_id IN (

SELECT sysjobsteps.job_id FROM msdb.dbo.sysjobsteps

JOIN msdb.dbo.sysjobs

ON sysjobs.job_id = sysjobsteps.job_id

WHERE sysjobs.job_id = @JOB_ID )

DELETE FROM msdb.dbo.sysjobstepslogs

WHERE step_uid IN(

SELECT sysjobstepslogs.step_uid FROM msdb.dbo.sysjobstepslogs

JOIN msdb.dbo.sysjobsteps

ON sysjobsteps.step_uid = sysjobstepslogs.step_uid

JOIN msdb.dbo.sysjobs

ON sysjobs.job_id = sysjobsteps.job_id

WHERE sysjobs.job_id = @JOB_ID )

DELETE FROM msdb. dbo.sysmaintplan_subplans

WHERE job_id IN (

SELECT sysmaintplan_subplans.job_id FROM msdb.dbo.sysmaintplan_subplans

JOIN msdb.dbo.sysjobs

ON sysjobs.job_id = sysmaintplan_subplans.job_id

WHERE sysjobs.job_id = @JOB_ID )

DELETE FROM msdb.dbo.sysjobs WHERE job_id = @JOB_ID

Step 5

Run the stored procedure passing in the job id. Why am I not using the name, wouldn’t that be friendlier? Of course it would, but then people make typos so asking you to copy the GUID is a safer, more reliable bet. Also, keep in mind that this script deletes any steps that might be a “subplan” of a particular maintenance plan so take care to delete or recreate and maintenance plans that you might have.

sp_ForceJobDelete ‘paste job id here’

Popularity: 7% [?]