Scheduling of jobs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Scheduling of jobs

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-08 : 09:49:14

Philip writes "Is it possible to schedule a job to run at less than a minute. I have one that needs to run every 15 seconds or so but I can't work out how to do it. I figure that the freq_subday_type and freq_subday_interval fields in sysjobschedules have something to do with it, but no matter what I try I can't get it to run quicker than every minute.

Your help and advice will be much appreciated as this is a systems architecture influencing problem.

Thanks"

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-04-08 : 09:51:09

The short answer is no, but use can write a stored procedure that fires code more often. Use the WAITFOR command to run something every 15 seconds, and then schedule the sproc for every minute.

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-08 : 11:21:55

You may also be able to setup 4 schedlues for the job and stagger the start times. I don't think this is possible through the EM GUI because you can't specify seconds, but it should be possible through the job stored procs. I haven't tested this, so let me know whether or not it works.

Schedule 1: Occurs every 1 days, every 1 minutes, between 12:00:00 AM and 11:59:59 PM
Schedule 2: Occurs every 1 days, every 1 minutes, between 12:00:15 AM and 11:59:59 PM
Schedule 3: Occurs every 1 days, every 1 minutes, between 12:00:30 AM and 11:59:59 PM
Schedule 4: Occurs every 1 days, every 1 minutes, between 12:00:45 AM and 11:59:59 PM

Jeff Banschbach
Consultant, MCDBA

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-08 : 11:37:12

I was curious, so I tested this theroy and it worked like a dream. The job fired every 15 seconds. I setup the job in the GUI with 4 schedules firing every minute from 12:00:00 AM to 11:59:59 PM. Then scripted the job, modified the active_start_time, and updated the job. Here's the schedule lines I used.

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = N'Staggered Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20020408,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = N'Staggered Schedule 2',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20020408,
@active_start_time = 15,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = N'Staggered Schedule 3',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20020408,
@active_start_time = 30,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = N'Staggered Schedule 4',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20020408,
@active_start_time = 45,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959

Jeff Banschbach
Consultant, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources