Delete SQL Agent Jobs

I’ve recently been tasked with standardizing SQL maintenance jobs for a farm of several dozen SQL servers.  The previous DBA had a mixture of maintenance plans and custom scripts in place for scheduled maintenance.  I knew ahead of time that I wanted to use a Central Management Server and then mass deploy my maintenance job scripts (I use Ola Hallengren’s scripts).

I ran into a problem where I prematurely published maintenance scripts to all servers.  I also had some jobs enabled by default, while others were disabled by default.  The disabled jobs were scripts that were not quite ready for production.  In hindsight, I wished that I had never deployed those scripts.  So, I had the idea “Let’s delete every disabled job from every server”.  Doing so would allow me to start with a clean slate.

There is a system stored procedure for deleting jobs:   sp_delete_job

One caveat with deleting jobs is the @delete_unused_schedule parameter.  If unspecified, the stored procedure will delete the schedule that is associated with the deleted job (given that no other jobs are associated to the schedule).  I found out the hard way.  See the above link for writing your own job deletion code, or read further for a script to delete all disabled jobs from a server.  You can also run this on your Central Management Server to delete disabled jobs from all registered servers.

/***

Delete all disabled jobs.

Use a cursor to delete each job that is currently disabled.

Gary Gwyn
https://sqlexchange.wordpress.com
1/21/14
*************************/
DECLARE @j UNIQUEIDENTIFIER;
DECLARE @name VARCHAR(100);

DECLARE j_cursor CURSOR
FOR
SELECT job_id, NAME
FROM msdb..sysjobs
WHERE enabled = 0

OPEN j_cursor

FETCH NEXT
FROM j_cursor
INTO @j, @name

WHILE @@fetch_status = 0
BEGIN
 EXEC msdb..sp_delete_job @job_id = @j, @delete_unused_schedule = 0

PRINT 'Deleted job: ' + @name

FETCH NEXT
 FROM j_cursor
 INTO @j, @name
END

CLOSE j_cursor

DEALLOCATE j_cursor