Cannot drop job under SQL Server Agent

Question

First, I could not save changes to the schedule. It was giving me the error everybody was mentioned about 32 digits and 4 dashes

Second, I deleted the job under Management Plan and I wanted to delete it under SQL Server Agent, but it gave me error that delete statement conflicted with the reference constraint "FK_subplan_job_id".

How can I resolve it?

Answer 1

The error message says that  "a job can't be deleted until a maintenance plan has been deleted."

Can you try to delete the maintenance plan, which will then automatically delete any jobs related to that plan.

 

Thanks

Rajesh

Answer 2

I have the same issue. The maintenance plan is gone, but the job still exists under SQL Server Agent and cannot be dropped.

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

Drop failed for Job 'Daily Maintenance.Dumps'.  (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_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)

 

Answer 3

The issue is caused by foreign keys existing on the sysmaintplan_log and sysmaintplan_subplans tables
which prevent you from directly deleting the job in sysjobs.

 

Please follow the steps below to manually remove the job entry.

Before performing these steps, Please take a backup of MSDB database.

 

Open SQL Server Management Studio (SSMS), launch a new query

 

use msdb
go

 

select * from sysjobs

-- Find the job_id for the job you wish to delete from the output above.

 

-- Wrap statements in a transaction for protection

 

begin tran

 

select subplan_id from dbo.sysmaintplan_subplans where job_id = '<Insert job_id that you wish to delete>'

 

delete from dbo.sysmaintplan_log where subplan_id = '<Insert subplan_id  from select
statement above>'

2) Delete the entry in sysmaintplan_subplans:

delete from dbo.sysmaintplan_subplans where job_id = '<Insert job_id that you wish to delete>'

3) Delete the main job entry in sysjobs:

delete from dbo.sysjobs where job_id = '<Insert job_id that you wish to delete>'

 

4) If everything works fine, please commit the transaction:

commit tran

 

Let me know how it goes..

 

Thanks

Rajesh

Answer 4

Rahesh,

This worked just fine. Thank you. I successfully deleted this job, and created 2 new jobs - one for full backup of database, and another - for backup of tran logs. The full backup is scheduled to run once every morning, and it runs just fine. However, tran log backup, which is scheduled to run every hour, has some problems. Sometimes it runs successfully 3-4 times and failing after that with this error:

Executing the query "BACKUP LOG [survey_p0037832] TO  DISK = N'G:\\database backups\\logs\\survey_p0037832_backup_200708021000.trn' WITH NOFORMAT, NOINIT,  NAME = N'survey_p0037832_backup_20070802100002', SKIP, REWIND, NOUNLOAD,  STATS = 10

" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.

BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

After I execute the full database backup on demand, then tran logs backup is runnung fine again for next 3-4 times...  We do not have any conflict in time, I avoided that scenario... Do you know how can I fix it? Thank you

 

Answer 5

Hi Rajesh,

Actually there are no corresponding rows in the sysmaintplan_subplans table for the jobs that got orphaned from the deletion of maintenance plans. And I am unable to delete the jobs showing up in the agent or using sql to delete the row in sysjobs table due to the same error message seen in this thread.

Any ideas on how to fix ?

Thanks very much.

Kumar.

Answer 6

Here it is eight years later and I find myself in the same situation as JulieShop.  I used your technique and it worked great in my case as well.  Thank you!

---

Jim Drewe

Answer 7

Hello,

With SQL 2008 R2, before "delete from dbo.sysjob" command, I had to run

delete from [msdb].[dbo].[sysjobschedules]
 where job_id = '<Insert job_id that you wish to delete>'

because of a another foreign key conflict in that table.

Then it worked fine !

Answer 8

some time the job is linked with sysmaintplan_subplans table also. So try this one.

make a backup of msdb, as described above and then try

use msdb
;
begin transaction
;

// GET THE LIST OF JOB ID's FROM THIS QUERY AND SELECT THE JOB_ID YOU WANT TO DELETE

select * from dbo.sysjobs order by name
;
select * from dbo.sysjobs where job_id='05E1C7BE-8FCB-4C37-BCFF-B780B6D35AE0'
;
select * from dbo.sysmaintplan_subplans where job_id='05E1C7BE-8FCB-4C37-BCFF-B780B6D35AE0'
;
select * from dbo.sysjobschedules where job_id='05E1C7BE-8FCB-4C37-BCFF-B780B6D35AE0'
;
delete from dbo.sysjobschedules where job_id='05E1C7BE-8FCB-4C37-BCFF-B780B6D35AE0'
;
delete from dbo.sysmaintplan_subplans where job_id='05E1C7BE-8FCB-4C37-BCFF-B780B6D35AE0' 
;
delete from dbo.sysjobs where job_id='05E1C7BE-8FCB-4C37-BCFF-B780B6D35AE0' 
;
commit transaction
;

replace your job_id with the sample given in this example job_id

Answer 9

Thank you!!!  

I had to delete a line out of the sysjobssched table too.