SQLGeeksPro

<< Previous
This article is in continuation of Script to Delete SQL Agent Jobs. If you have landed here directly you might want to check the above article if the first script helps.
Anyways in this continuation article I am going to provide you the script to Delete SQL Agent Jobs in a scenario when you need to delete all the jobs except the default one “syspolicy_purge_history”.

Script to Delete All SQL Agent Jobs except the default one:

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name <> 'syspolicy_purge_history' 
   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 result set will provide you with the SQL delete statements if you run the above query as is. All you need to do is copy the output and  execute them in the SSMS.

Sample result set as shown in the following screenshot:

There is another way to execute the query. You can directly delete the jobs without generating the delete statements. If you have little T-SQL knowledge you can modify the same query to do it. If you are an accidental DBA, no worries just remove the -- sign from the statement EXEC (@SQLStatement) and comment the statement PRINT(@SQLStatement). I have explained this already in the previous article so if you can refer that.

Now let’s talk about a scenario when you need to delete all sql agent jobs except the replication, mirroring, log shipping or except few jobs.
Yes, you got it right. All you need to do is modify the where condition. Let me show you an example of Deleting All SQL Agents Jobs except the replication ones. The below query shows example of jobs related to transaction replication and the default one “syspolicy_purge_history".

Script To Delete All SQL Agent Jobs Except Few:

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name not in
('syspolicy_purge_history' 
,'Agent history clean up: distribution' 
,'Distribution clean up: distribution' 
,'Expired subscription clean up' 
,'INDSBLRHT0VR72-AdventureWorks2017-2' --Change the job name as per your environment
,'INDSBLRHT0VR72-AdventureWorks2017-Adven_Pub-2' --Change the job name as per your environment
,'INDSBLRHT0VR72-AdventureWorks2017-Adven_Pub-INDSBLRHT0VR72-5' --Change the job name as per your environment
,'Reinitialize subscriptions having data validation failures' 
,'Replication agents checkup' 
,'Replication monitoring refresher for distribution' 
)

   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

Another way of achieving the same end result is using the first query and filter out the jobs from the result set.
Hope this helps. Feel free to leave your comments.
You also might find the below scripts useful:

Query To Retrieve All Permissions for a user in Selective databases
Script to Rename SQL Agent Jobs
Script to get SQL Cluster Failover Time and Node Name
Script to find the SQL Agent Job Name in Execution
List all permissions for a user in all or selective databases