How to clear SQL Agent Job Scheduler Cache

Question

I have to submit tickets to alter job scheduling, so rather than a walk through of the GUI I try to update msdb..sysschedules via tsql script. I always see the alterations immediately via the GUI. The first time I used this method though, it took a day for the changes to propagate. More recently it's been longer. I suspect something's cached in agent/scheduler, but I can't find documentation on what to do to flush this in the least intrusive manner on a production system. I was hoping something rudimentary like disable/enable the job would trigger a refresh, but it did nothing and I'd rather issue something in T-SQL with my tickets to ensure changes get used. I've requested a restart of agent when nothing's on the horizon to see if that works, but ideally would prefer something less intrusive. This is a 2014 instance.

A sample script:

USE msdb;
update s set
    s.freq_subday_interval=3
from msdb..sysjobs j
    left join msdb..sysjobschedules js on j.job_id = js.job_id
    left join msdb..sysschedules s on js.schedule_id = s.schedule_id
    where j.name ='SomeDb.RunMeEvery3MinsBusta';

----edit-----

I guess taking from answers provided I tested below and it seems to cascade immediately. Just wanted to make it a little more dynamic.

declare @jobid nvarchar(50), @scheduleid int
select @jobid=j.job_id,@scheduleid=s.schedule_id from msdb..sysjobs j
    left join msdb..sysjobschedules js on j.job_id = js.job_id
    left join msdb..sysschedules s on js.schedule_id = s.schedule_id
    where j.name ='test';

EXEC msdb.dbo.sp_attach_schedule @job_id=@jobid,@schedule_id=@scheduleid
EXEC msdb.dbo.sp_update_schedule @schedule_id=@scheduleid, 
        @freq_subday_interval=1
GO

Answer 1

You may use below script to alter the schedule:

EXEC msdb.dbo.sp_attach_schedule @job_id=N'533fb186-72e9-40c5-af88-c8e57f097126',@schedule_id=16
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_schedule @schedule_id=16, 
        @active_start_time=24500
GO

you may find job_id using below script for the job you want to change schedule:

select job_id from msdb..sysjobs where name = 'SomeDb.RunMeEvery3MinsBusta'

To find schedule_id, please use below script:

select * from msdb..sysjobschedules where job_id = '533FB186-72E9-40C5-AF88-C8E57F097126'

Here job_id is taken from previous script of table sysjobs.

I have specified this job to run at 2:45AM on daily basis, you may specify other options too.

EXEC msdb.dbo.sp_attach_schedule @job_id=N'533fb186-72e9-40c5-af88-c8e57f097126',@schedule_id=16
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_schedule @schedule_id=16, 
        @freq_type=8, 
        @freq_interval=64, 
        @freq_recurrence_factor=1, 
        @active_start_time=153000
GO

Above script is to change job type to weekly, running at 3:30PM on Saturdays.

Hope above helps.

Answer 2

Implied by Learning_DBAdmin's answer, what you do is not supported. Modifying the system tables directly, that is. You should use the interface that is provided for us, the agent stored procedures.

FWIW, if you read the source code, for instance sp_update_jobschedule, you'll find a call to sp_sqlagent_notify. This isn't documented, however, for above reason.