How to quickly disable multiple SQL Agent jobs on the server.

If you have ever needed to quickly disable a group of SQL Agent jobs without turning the agent off totally, there is a stored procedure out there that will allow you to turn the jobs off programmatically. If you have your agent jobs categorized, and only want to turn off a certain category (application processes, for instance) but leave replication jobs running, you can run the following script to find the enabled jobs that are in that category:

SELECT SJ.Name, SJ.[Enabled], SC.Name

  FROM msdb..sysjobs SJ inner join msdb..syscategories SC

    on SJ.category_id = SC.category_id

 Where SJ.[Enabled] = 1

   and SC.Name = '<CategoryName>'

Once you’ve reviewed the jobs to be disabled, run the following script to generate the code needed to disable all the jobs in the category. Since you will be running the output of the following script, you will have the ability to control which jobs are disabled.

Select 'Exec MSDB.dbo.sp_update_job @job_name = N''' + SJ.Name + ''', @Enabled = 0'

  FROM msdb..sysjobs SJ inner join msdb..syscategories SC

    on SJ.category_id = SC.category_id

 Where SJ.[Enabled] = 1

   and SC.Name = '<CategoryName>'