SQL Server Performance Forum – Threads Archive

deleting the job

I am trying to delete a tlog backup job and it gives me the following error. The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb" ,table
"dbo.sysmainplan_subplans", column "job_id".
The statement has been terminated. (Microsoft.SQL Server, error:547) Can anyone help me in resolving this issue regards

---

Why you are attempting to drop by accessing the system table, use SP_DELETE_JOB. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Are you issuing a delete query against a system table? Please use the appropriate system stored procedure to drop a job (sp_delete_job in SQL 2000)

---

I believe she was trying to delete a row from the table…
Please use sp_delete_job (2000 and 2005) as Adrian suggested or use EM… Is this server 2000?, please move this to 2000 group… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

I am not trying to delete a row from the system table. this is the error i am getting when i try to delete the job from management studio.

---

What SP do you have?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

It is giving me the same error even if i use sp_delete_job 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.

---

I mean: What Service Pack do you have? Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

SP1

---

Some problems was fixed using sp2. I suggest to install it.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

thanks guys. I fixed the problem I used the sp_help_job and found out the jobid and deleted that job from sysmaintplan_subplans. then tried to delte that job from management studio and its done.

---

I believe in 2005 you can’t delete maintenance job without delete plan…
Try…
EXECUTE msdb..sp_delete_maintenance_plan ‘MP_Plan_id ‘
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

I deleted the maint plan first. after that when i tried to delete the job it gave me this error.

---

When you delete maintenance plan, it suppose to delete the job unless you change the job name or catogery etc…. EXECUTE msdb..sp_delete_maintenance_plan procedure executes the following statement you can try manually to the data from system tables… /* clean the related records in sysdbmaintplan_database */
DELETE FROM msdb.dbo.sysdbmaintplan_databases
WHERE [email protected]_id
/* clean the related records in sysdbmaintplan_jobs*/
DELETE FROM msdb.dbo.sysdbmaintplan_jobs
WHERE [email protected]_id
/* clean sysdbmaintplans */
DELETE FROM msdb.dbo.sysdbmaintplans
WHERE plan_id= @plan_id
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

thank you so much for the help

---

Did you resolve your issue? if yes… can share the solution <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

---

I deleted the maint plan first. after that when i tried to delete the job it gave me this error. .,.. using other way round will solve the issue. … here is what she did. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>