This article is about a simple script to delete SQL Agent Jobs. Recently I had faced a situation when I had to remove more than 100 SQL Agent jobs.
First of all let me tell you that there may be different scenarios when you may need to delete all SQL Agent jobs, few of them or all except few required jobs etc. I have tried to include couple of queries in this article.
I had blog about renaming several SQL Agent jobs. All I have done is little modification in the query.
Script To Delete SQL Agent Jobs Having Particular Naming Convention
DECLARE @SQLStatement VARCHAR(200) DECLARE @jobName SYSNAME DECLARE c1 CURSOR FOR SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE '%DBA%' --Change the LIKE Operator as applicable OPEN c1 FETCH NEXT FROM c1 INTO @jobName IF @@CURSOR_ROWS=0 PRINT 'No Job found! Please re-check LIKE operator.' WHILE @@fetch_status=0 BEGIN SET @SQLStatement= 'EXEC msdb.dbo.sp_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1' PRINT(@SQLStatement) --EXEC (@SQLStatement) --Uncomment to Execute FETCH NEXT FROM c1 INTO @jobName END CLOSE c1 DEALLOCATE c1
The following screenshot shows SQL Agent Jobs having naming convention like ‘1_DBA job Name’, ‘2_DBA job Name’ and so on. This is just for example, you may have different names in your environment.
Besides that there is another way you can use this query. The result set will provide you with the SQL delete statements if you run the above query as is. Therefore all you need to do is copy the output and execute them in the SSMS.
The following screen shot shows the result set:
Now lets talk about the other way of using the query. If you uncomment the statement EXEC (@SQLStatement) and comment the PRINT(@SQLStatement) statement and then execute the query it will directly delete the jobs.
The output will not provide any sql statements as shown in the following screen shot. Therefore you should run the query this way only if you are pretty sure what you are doing.
Now lets see another scenario like when you need to delete all the jobs except the default one ‘syspolicy_purge_history’ job Click Next Page >>
