CODEWITHSUNDEEP
sql-serversql-server-2012sql-agent-jobsql-agent
Quicksilver
Quicksilver

Reputation: 295

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

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.

Upvotes: 2

Views: 4645

Answers (1)

gofr1
gofr1

Reputation: 15977

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.

Upvotes: 1

Related Questions