SQL Server Agent jobs running even though I disabled the schedule in script

Question

I need to disable all jobs running on a server. I ran the following code:

   declare @t table (schedule_id int, [name] varchar(300))

   update msdb.dbo.sysschedules 
          set enabled = 0
   output inserted.schedule_id, inserted.name into @t
   from msdb.dbo.sysjobs j


   join msdb.dbo.sysjobschedules js
   on j.job_id = js.job_id
   join msdb.dbo.sysschedules s 
   on js.schedule_id = s.schedule_id
   where j.[name] not like 'DB -%' and j.[name] not like 'sys%' and j.[name] not like 'Maintenance%'

   select * from @t

When I check a given job and its associated schedule, either from the SQL Agent GUI, or through SQL, I can see the schedule is disabled. However, the jobs are still running, and I can see next run dates in the future if I check msdb.dbo.sysjobschedules.

The only things I've seen online suggest that there's a background thread that needs to be refreshed to pick up this change? And that do that, I need to run sp_sqlagent_refresh_job (did not help), or restart the server.

I can restart the server, but I'm curious if anyone else has seen this behavior, knows the cause, knows how to fix it another way.

score 1 · Answer 1 · answered Oct 09 '16 at 12:18

1

Why are you bothering with schedules? Use sp_update_job with dynamic SQL to disable all jobs:

DECLARE @sql nvarchar(max)

;WITH cte AS (
SELECT  j.job_id,
        j.[name]
FROM msdb.dbo.sysjobs j
WHERE j.[name] not like 'DB -%' 
    and j.[name] not like 'sys%' 
    and j.[name] not like 'Maintenance%'
)

SELECT @sql = (
    SELECT 'EXEC msdb.dbo.sp_update_job @job_id = '''+CAST(job_id as nvarchar(max))+''', @enabled = 0;'+CHAR(10)
    FROM cte
    FOR XML PATH('')
)

EXEC sp_executesql @sql

If you PRINT @sql before EXEC you will see generated query:

EXEC msdb.dbo.sp_update_job @job_id = 'CCCF8FC0-FCD4-4260-9A48-518AF5826297', @enabled = 0;
EXEC msdb.dbo.sp_update_job @job_id = '48BB41E6-6BEC-452B-8D42-760AECDBB808', @enabled = 0;

If you need to stop jobs use sp_stop_job.

answered Oct 09 '16 at 12:18

gofr1

15,741
11
42
52

That's a good solution, thanks. I'm mainly curious about what I missed that causes the schedules to keep running. I wonder if I cleared out the next run dates from msdb.dbo.sysjobschedules, that would do it? – Quicksilver Oct 10 '16 at 19:32
1

You was on the right way, there is one note about that table: it refreshes every 20 minutes. So if you disabled schedules in sysschedules, the sysjobschedules will refresh in next ~20 minutes, so you cannot see jobs schedules change immediately. – gofr1 Oct 10 '16 at 19:46
And even if you disable schedules the jobs, that started earlier will run. To stop them use sp_stop_job. – gofr1 Oct 10 '16 at 19:50

SQL Server Agent jobs running even though I disabled the schedule in script

1 Answers1