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 2005 Forums

SQL Server Administration (2005)

jobs

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-09 : 11:02:01

How is it possible to schedule a job to run every 40 seconds.
It seems every 1 minute is the shortest time available in the job properties for scheduling a job.
Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-09 : 11:32:37

I think every minute is the highest freq that agent supports.

elsasoft.org

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-09 : 11:45:51

I have heard before that there is a way to reduce it to seconds.
Just wondering if anyone knows how this is possible.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-09 : 11:59:04

Minutes are the smallest repeat interval that you can specify on a job.

You can get a job to run every 40 seconds by setting up 3 different schedules:
1. Schedule 1 starts at 00:00:00 and repeats every 2 minutes.
2. Schedule 2 starts at 00:00:40 and repeats every 2 minutes.
3. Schedule 3 starts at 00:01:20 and repeats every 2 minutes.

Edit:
The following is a more general solution to this problem. The query determines how many schedules you would have to setup to be able to run a job on a cycle of every N seconds.


declare @seconds	int
declare @maxnum		int

-- Set number of seconds between job executions
set @seconds = 40
set @maxnum = (@seconds*60)+1

select	top 1
	
	NUMBER_OF_SCHEDULES_NEEDED = a.NUMBER
from
	-- Number Table Function available on this link
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
	F_TABLE_NUMBER_RANGE(1,@maxnum) a
where
	(a.NUMBER*@seconds)%60 = 0
order by 
	a.NUMBER


Results:
NUMBER_OF_SCHEDULES_NEEDED 
-------------------------- 
3

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources