<< 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
Note: Principal Server has written the log records to the log file. So one of the misconception is cleared here. Principal Server does not wait for acknowledgement from mirror to commit the transaction.
