none
How can i delete a job RRS feed

  • Question

  • How can i delete a job (which Scheduled for taking T-Log backups) created in maintenance plans.

    In error message it shows need to drop a foreign key in system databases.

    How can i drop this job with out making any changes or deleting any Constraints in the system database(MSDB).

    Please do me a need full....

    Thanks 


    Tuesday, March 11, 2014 8:22 AM

Answers

  • Hi,

    The reason you receive the error when trying to delete the job is because the job is part of a maintenance plan. The job represents a specific subplan in a maintenance plan, and there is a reference between the job and the subplan. If you want to delete the job, you should simply go to the maintenance plan and either delete the corresponding subplan or remove the schedule from the subplan. This will automatically affect the corresponding job. No need to delete maintenance plan log manually or anything like it...

    Regarding your second question, the fact that you don't see the 3 databases you're looking for when you configure the log backup task has nothing to do with the job you deleted. Most probably, the recovery model of these databases is set to "Simple", which doesn't allow log backups to be taken. You should first change the recovery model of these databases to "Full" or "Bulk-Logged", and then you should be able to see them in the backup log task.

    Good luck!

    --------------------------------------------
    Guy Glantser
    SQL Server Consultant & Instructor
    Madeira - SQL Server Services
    http://www.madeira.co.il

    Tuesday, March 11, 2014 10:14 AM

All replies

  • Try with sp_delete_job system procedure.

    Ref: http://technet.microsoft.com/en-us/library/ms188376.aspx

    eg:

    EXEC sp_delete_job
        @job_name = N'yourjobname' ;


    Tuesday, March 11, 2014 8:33 AM
  • see

    http://dbamohsin.wordpress.com/tag/delete-maintenance-subplan/


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, March 11, 2014 9:01 AM
  • Use Msdb 

    go

    EXEC sp_delete_job
        @job_name = N'test' ;


        Msg 547, Level 16, State 0, Procedure sp_delete_job, Line 182
    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.

    Getting the same error bro...

            
    Tuesday, March 11, 2014 9:03 AM
  • Use Msdb 

    go

    EXEC sp_delete_job
        @job_name = N'test' ;


        Msg 547, Level 16, State 0, Procedure sp_delete_job, Line 182
    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.

    Getting the same error bro...

            

    Did you try the method in link I posted?

    http://dbamohsin.wordpress.com/tag/delete-maintenance-subplan/



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, March 11, 2014 9:11 AM
  • ya bro , its working but when i used deleted statement it showed

    485 rows affected

    ----->  Can i know what it is and It doesn't affect server right...

    Tuesday, March 11, 2014 9:18 AM
  • It will delete the job entry from mainetenanceplan and subplan catalog views. BY default when you try it from UI or using system sp it doesnt do it thats why you get error. do this and then delete your job and it will work fine.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, March 11, 2014 9:35 AM
  • It mean it does not effect the Transaction log backup's which are done , Am i right bro.

    And i got another issue here , when i deleted the Subplan_3 (Used for T-Log backups) ,

    It does not showed a 3 databases which are in Online,

    The same 3 databases  i have scheduled for Subplan_1 (Used for  FULL backups) And  Subplan_2 (Used for Differential backups) .

    Tried a lot by refreshing the server but still am not unable to see the 3 databases when i select Transaction log backup in maintenance paln  ..


    Tuesday, March 11, 2014 9:43 AM
  • Nope..its just job related metadata you're changing here. It doent affect any actual steps done by job.

    Sorry didnt get second part. How did you delete it? from ui or using code?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, March 11, 2014 9:50 AM
  • Actually I created Backup plan in that i have 3 Subpalns.

     Subplan_1 (Used for  FULL backups),

     Subplan_2 (Used for Differential backups) ,

    Subplan_3 (Used for T-Log backups).

     Here , i deleted a Subplan_3 (Used for T-Log backups)  by using  your Link.

     And , when i started creating a T-log backup using maintenance plan it is not showing 3 databases which i want to take a T-log backup.

    The same databases have been checked by me for Full and Differential  backup's.

    Tuesday, March 11, 2014 10:00 AM
  • Using Code i deleted it bro...


    Tuesday, March 11, 2014 10:06 AM
  • Hi,

    The reason you receive the error when trying to delete the job is because the job is part of a maintenance plan. The job represents a specific subplan in a maintenance plan, and there is a reference between the job and the subplan. If you want to delete the job, you should simply go to the maintenance plan and either delete the corresponding subplan or remove the schedule from the subplan. This will automatically affect the corresponding job. No need to delete maintenance plan log manually or anything like it...

    Regarding your second question, the fact that you don't see the 3 databases you're looking for when you configure the log backup task has nothing to do with the job you deleted. Most probably, the recovery model of these databases is set to "Simple", which doesn't allow log backups to be taken. You should first change the recovery model of these databases to "Full" or "Bulk-Logged", and then you should be able to see them in the backup log task.

    Good luck!

    --------------------------------------------
    Guy Glantser
    SQL Server Consultant & Instructor
    Madeira - SQL Server Services
    http://www.madeira.co.il

    Tuesday, March 11, 2014 10:14 AM
  • wow... Thanks Guy

    Tuesday, March 11, 2014 11:02 AM