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
Another way to delete multiple rows is to use the IN operator. DELETE FROM table_name WHERE column_name IN (value 1, value 2, value 3, etc...); If you want to delete all records from the table then you can use this syntax.
The syntax also supports deleting rows from multiple tables at once. To delete rows from both tables where there are matching id values, name them both after the DELETE keyword: DELETE t1, t2 FROM t1 INNER JOIN t2 ON t1.id = t2.id; What if you want to delete nonmatching rows?
Using SQL Server Management StudioExpand SQL Server Agent, and then expand Jobs. Right-click a job and click View history. In the Log File Viewer, select the job for which you want to clear history, and then do one of the following: Click Delete, and then click Delete all history in the Delete History dialog.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With