Disable all SQL Agent Jobs

Recently, I was looking for an easy way to disable all of the SQL Server Agent jobs as part of a migration to a new environment. My first thought was to just stop the SQL Agent service, but there were some jobs that were going to be used during the migration, so that was out.

Not wanting to reinvent the wheel, I ran a quick search to see if anyone had posted an example of this before.  I did find some results, but everything I found either used a cursor or updated system tables directly.  A cursor would be fine for this task performance-wise, but I hate using cursors if I don’t need to, and here you really don’t need to.

Here’s the simple script I came up with:

declare @sql nvarchar(max) = '';
select
@sql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;
' from msdb.dbo.sysjobs
where enabled = 1
order by name;

print @sql;
exec (@sql);

 
This queries the sysjobs table creates an exec statement of sp_update_job for each active job, then runs the batch.

exec msdb.dbo.sp_update_job @job_name = 'SQLAgentJob1', @enabled = 0;
exec msdb.dbo.sp_update_job @job_name = 'SQLAgentJob2', @enabled = 0;
exec msdb.dbo.sp_update_job @job_name = 'SQLAgentJob3', @enabled = 0;
…

 
You can use the same technique to avoid cursors in other situations – I’ll blog about that another time.

This entry was posted in Uncategorized. Bookmark the permalink.

5 Responses to Disable all SQL Agent Jobs

  1. Ben Bagheri says:

    A simpler way:

    UPDATE MSDB.dbo.sysjobs
    SET [enabled] = 0
    WHERE [enabled] = 1

    • David Howard says:

      Thanks for the comment, Ben. That is simpler, but as I mentioned in the post, I wanted to avoid updating system tables directly.

  2. Henry says:

    Bens simpler way will not work, try it and you will see. Only way is to do it David’s way

  3. Jaco says:

    Bens simpler way does not work in SQL2012. It only marks the jobs as disabled in the SQL Server Agent Job list, but the jobs will still run as scheduled

  4. Alex says:

    Thanks, the script from David worked perfectly.

Leave a Reply

Your email address will not be published. Required fields are marked *