I was doing some heavy lifting for a customer recently... it was after hours, and I needed to disable a few Agent jobs beforehand, to be sure I didn't run into anything. This post is the cursor I used to first disable the jobs, and then re-enable them afterward. Very simple, you can see it's just based on an IN list:
/* Use this to disable multiple SQL
Server Agent jobs in one action. */
USE msdb;
DECLARE
@job VARCHAR(128),
@success
INT,
@error INT,
@sql NVARCHAR(255)
/* declare cursor to disable sysjobs based on the IN list
*/
DECLARE DisableJobCursor CURSOR
FOR
SELECT sj.name FROM msdb..sysjobs sj
WHERE sj.name NOT IN('AFewJobs','YouMayWant','ToIgnore') -- change as needed
AND enabled = 1
SELECT @success = 0
SELECT @error = 0
OPEN DisableJobCursor
FETCH NEXT FROM DisableJobCursor
INTO @job
WHILE (@@FETCH_STATUS <>
-1)
BEGIN
IF (@@FETCH_STATUS <>
-2)
BEGIN
SELECT @sql='EXEC dbo.sp_update_job @job_name = N'''+@job+''',@enabled = 0;'
EXECUTE sp_executesql @sql
SELECT @success =
@success + 1
END
NextRecord:
FETCH NEXT FROM DisableJobCursor
INTO @Job
END
CLOSE DisableJobCursor
DEALLOCATE DisableJobCursor
SELECT 'Disabled Jobs = '
+ CONVERT(VARCHAR(10),@success)
SELECT 'Failures = ' + CONVERT(VARCHAR(10),@error)
If you're working with a lot of jobs, that IN list can become a pain. In that case we could go in more collectively by using the job
category. For example,
let's say we want to disable all jobs in the 'Database Maintenance' category. Just use @category in your cursor declaration, instead of the IN
list, like this:
DECLARE
@job VARCHAR(128),
@jobcategory VARCHAR(25) = 'Database Maintenance',
@sql VARCHAR(255)
DECLARE DisableJobCursor CURSOR
FOR
SELECT sj.name FROM msdb..sysjobs sj
INNER JOIN msdb..syscategories sc
ON sj.category_id = sc.category_id
WHERE sc.name = @jobcategory
AND enabled = 1 Another option, though a bit reckless, is just to go in for all enabled jobs. This approach let's you disable/enable en masse:
DECLARE DisableJobCursor CURSOR
FOR
SELECT sj.name FROM msdb..sysjobs sj
WHERE sj.enabled = 1
You want to be careful with that last approach... who knows how many jobs you have?!
I would be remiss if I don't caution you about cursor usage. In most cases, I am pretty anti-cursor, but... that's not what this post is about. I will give you an excellent reference for cursor examples, performance considerations, and alternatives. Don't ignore. This is an excellent read.
No comments:
Post a Comment