which is the correct way to delete multiple jobs in sql server

Question

Im using SQL Server 2012 and I have a case where I have to delete sql jobs. I found two approaches which are as follows:

Approach 1

DECLARE @jobId binary(16)  

WHILE (1=1)
BEGIN
    SET @jobId = NULL
    SELECT TOP 1 @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name like N'Abc%') 

    IF @@ROWCOUNT = 0
        BREAK

    IF (@jobId IS NOT NULL) 
    BEGIN     
        EXEC msdb.dbo.sp_delete_job @jobId 
    END 
END

Approach 2

DECLARE @listStr VARCHAR(MAX)=null

SELECT @listStr = COALESCE(@listStr+'exec msdb.dbo.sp_delete_job ' ,'') + '''' + convert(varchar(max),job_id) + '''; '
FROM msdb.dbo.sysjobs WHERE (name like N'$(TestPublisherServer)-$(TestPublisherDB)%') 

IF @listStr is not null
BEGIN 
    PRINT 'exec msdb.dbo.sp_delete_job ' + @listStr
    EXEC ('exec msdb.dbo.sp_delete_job ' + @listStr)
END

Both the approaches will delete the jobs, but I want to know which is the best way or you can suggest me more efficient or correct ways to delete the jobs.

And one more question is, do we have to stop/disable the job before deleting it.

TIA Harsha

stb · Accepted Answer · 2012-08-03T06:30:34.423

4

I would use approach 1 because it is just simple and effective. There is no need to disable the job before. If you delete a job while it is running, the job will terminate with an error, so check if thats requirement for you.

Way to handle this problem(posted by OP):

IF exists(
    select 1 from msdb.dbo.sysjobactivity activity 
    where activity.run_Requested_date is not null 
           and activity.stop_execution_date is null 
           and activity.job_id = @jobId) 
 exec msdb.dbo.sp_stop_job @job_id = @jobId

I'm not sure if you are worried about the schedule to stay. Depending on how @delete_unused_schedule is set, sp_delete_job will delete it by default.

See the stored procedure comment

@delete_unused_schedule   BIT   = 1     -- For backward compatibility schedules are deleted by default if they are not 
                                        -- being used by another job. With the introduction of reusable schedules in V9 
                                        -- callers should set this to 0 so the schedule will be preserved for reuse.

So this option will remain you flexible, but i think you will have no need for the schedule because you probably want to clean up your system.

edited Aug 03 '12 at 06:30

answered Aug 01 '12 at 06:54

stb

3,405
2
17
24

but someone said, we have to stop a running job before deleting it. Are you sure its not necessary to stop the job before deleting it. – Harsha Aug 01 '12 at 11:15
Sorry, i didn't read the 'stop' as stop.. I just tested your scenario with sql server 2008 and the job terminated directly after the job was deleted. So this can lead you to an unfinished job! Anyways, if you are aware of this, check the activity monitor if there is a running job, or check the schedules if there is something going to start. I think you should be fine this way. – stb Aug 01 '12 at 11:36
If you want to be totally save, you could use a loop to disable all jobs using `sp_update_job`. – stb Aug 01 '12 at 11:40
I added this part IF EXISTS(select 1 from msdb.dbo.sysjobactivity activity where activity.run_Requested_date is not null and activity.stop_execution_date is null and activity.job_id = 'jobId') EXEC msdb.dbo.sp_stop_job @ job_id = @ jobId, inside the loop, will this be ok to stop the job before deleting it, so that everything is cleaned up properly – Harsha Aug 01 '12 at 11:45
I'm not sure if there is a huge difference between `sp_stop_job` and deleting a job while it's runnng, but sure this sounds saver. Not sure if you were mistaken here, but you should change `activity.job_id = 'jobId` to `activity.job_id = @jobId ` – stb Aug 01 '12 at 12:17
@Harsha so did you come along with it? – stb Aug 02 '12 at 14:32
@Harsha You're welcome. I updated my answer and integrated our conversation results. – stb Aug 03 '12 at 06:33

which is the correct way to delete multiple jobs in sql server

1 Answers1