SQL Server Performance

deleting the job

Discussion in 'SQL Server 2005 General DBA Questions' started by preethi.talapanuri, Apr 17, 2007.

  1. preethi.talapanuri New Member

    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
  2. satya Moderator

    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.
  3. Adriaan New Member

    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)
  4. MohammedU New Member

    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.
  5. preethi.talapanuri New Member

    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.
  6. Luis Martin Moderator

    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.



  7. preethi.talapanuri New Member

    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.

  8. Luis Martin Moderator

    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.



  9. preethi.talapanuri New Member

  10. Luis Martin Moderator

    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.



  11. preethi.talapanuri New Member

    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.
  12. MohammedU New Member

    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.
  13. preethi.talapanuri New Member

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

    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 plan_id=@plan_id
    /* clean the related records in sysdbmaintplan_jobs*/
    DELETE FROM msdb.dbo.sysdbmaintplan_jobs
    WHERE plan_id=@plan_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.
  15. preethi.talapanuri New Member

    thank you so much for the help
  16. MohammedU New Member

    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 />
  17. satya Moderator

    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.

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

Share This Page