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.