Monthly Archives: January 2014

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

Tagged , , , ,

Format your SQL code

I love the look of neatly formatted source code, but I hate writing it!  For me personally, it is much easier to ready neatly aligned and properly formatted code.

There is a free tool available which will allow you to apply automatic formatting against new and existing scripts.  A plugin is available for SQL Management Studio and for my favorite text editor (Notepad++).  The application is called Poor Man’s T-SQL Formatter.  I love the look of capitalized reserved keywords.

Tagged ,