Is it possible to schedule a job to run every 10 seconds ??

Question

Is it possible to schedule a job to run every 10 seconds ??

Gareth Hay

SSC-Addicted

Points: 486
More actions
October 17, 2006 at 6:35 am

#153234

The interface only seems to allow the running of jobs every 1 minute - is there a way around this ???

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Check out sp_update_jobschedule in the books online.

The parameter @freq_subday_type allows for minutes and hours. But if you notice, there's a missing value in the list (0x02) which stands for seconds. I have never tested this but I got this info from someone who did so it must work... somehow.

debrucer1 SSCertifiable Points: 5366 More actions · Answer 2

I know that log shipping defaults certain jobs to every minute; because when it fails, it send three messages to my application event log and to the SQL Server log. Gets so big so fast... imagine if you're also getting phone calls or pages? LOL

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

The cheap way would be to schedule a 'master job' to run every minute....and for it to call the 'slave job' 6 times....seperate by a (near) 10second delay....trouble there is to ensure cumulative work load (in elapsed time) doesn't exceed 1 minute....you could however 'put' in a dynamic delay based upon the elapsedtime of any one step.

Either way, you do need however to cope with jobs which over run their "slot"...ie does it matter to you that step 6 of batch #1 runs after step 1 of batch #2???

vadba SSChampion Points: 11133 More actions · Answer 4

I just tried the sp_update_jobschedule suggestion, and it works.

I created a job that executes one step called 'Step1':

INSERT JobTest (message) VALUES ('Job was run')

I created a schedule called 'Schedule1'. I then saved the job.

Next, I altered the job using the code below.

--- This was the test table

DROP TABLE JobTest

Go

CREATE TABLE JobTest

(

id int IDENTITY(1,1) PRIMARY KEY

, dt datetime DEFAULT GetDate()

, userid varchar(50) DEFAULT suser_sname()

, message varchar(50)

)

GO

-- This is the code to modify the schedule

EXEC msdb.dbo.sp_update_jobschedule

@job_name = 'Job_JobTest'

, @name = 'Schedule1'

, @enabled = 1

, @freq_type = 4 -- daily

, @freq_interval = 2 -- daily

, @freq_subday_type = 2 -- Seconds?

, @freq_subday_interval = 10 -- every 10 seconds

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Thanx for the confirmation... but I knew that my source was extremely reliable on that one .

steve block SSCertifiable Points: 7698 More actions · Answer 6

Just curious, what kind of job needs to be run every 10 seconds?

Steve

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

sp_closePopUps

sp_BreatheIn, sp_breatheOut

sp_blink

sp_PostOnSqlServerCentral

sp_FetchNewSqlServerCentralThreads

just to name a few .

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 8

Using an infinite loop, combined with WAITFOR, you can do it.