Tags: according, documentation, freq_subday_interval, job, jobs, modify, programming, runs, seconds, sql, time

jobs and @freq_subday_interval = 9

On Programmer » SQL

5,456 words with 3 Comments; publish: Thu, 29 May 2008 01:22:00 GMT; (20062.99, « »)

Hello,

Thanks for taking the time to read my question.

I am trying to modify a job so that it runs every 9 seconds.

According to the documentation that is undocumented, i should be able to use

.sql.todaysummary.com.freq_subday_type = 2 ( for seconds)

and any integer value for the .sql.todaysummary.com.freq_subday_interval ( i chose 9), but when I

run the following code receive an error message:

sp_update_jobschedule --.sql.todaysummary.com.job_id = '898AF977-9D10-4551-9283-2D44F30D3291',

.sql.todaysummary.com.job_name = 'subminute_interval',

.sql.todaysummary.com.name = '10 sectest',

.sql.todaysummary.com.freq_subday_type = 2, -- set the frequency per day to seconds.

.sql.todaysummary.com.freq_subday_interval = 9 -- set frequency to every 9 seconds.

go

error message:

Server: Msg 14278, Level 16, State 1, Procedure sp_verify_jobschedule, Line

177

The schedule for this job is invalid (reason: The specified

'.sql.todaysummary.com.freq_subday_interval' is invalid.).

Is there a rational reason for this? Or, would it be better to stagger two

jobs?

Thanks

Chieko

All Comments

Leave a comment...

  • 3 Comments
    • Think this used to work (for v7 maybe) but doesn't for v2000.

      Don't think there is a way to get a job to run at sub minut frequency (could

      be wrong though.

      In any case it takes quite a while to put the job back into the scheduler

      when it's finished so it might not be a good way to schedule this sort of

      interval.

      I usually do it by scheduling a job to run every minute which runs an SP to

      loop running at the required interval. If the sp fails then it will be

      restarted by the job.

      This only works for jobs that call tsql but the sp could repeatedly start

      another job - but maybe that's getting a bit over complicated.

      Maybe you should think about writing a scheduler if you are doing a lot of

      this sort of thing.

      e.g.

      http://www.nigelrivett.net/Products/Scheduler.html

      "chieko" wrote:

      > Hello,

      > Thanks for taking the time to read my question.

      > I am trying to modify a job so that it runs every 9 seconds.

      > According to the documentation that is undocumented, i should be able to use

      > .sql.todaysummary.com.freq_subday_type = 2 ( for seconds)

      > and any integer value for the .sql.todaysummary.com.freq_subday_interval ( i chose 9), but when I

      > run the following code receive an error message:

      > sp_update_jobschedule --.sql.todaysummary.com.job_id = '898AF977-9D10-4551-9283-2D44F30D3291',

      > .sql.todaysummary.com.job_name = 'subminute_interval',

      > .sql.todaysummary.com.name = '10 sectest',

      > .sql.todaysummary.com.freq_subday_type = 2, -- set the frequency per day to seconds.

      > .sql.todaysummary.com.freq_subday_interval = 9 -- set frequency to every 9 seconds.

      > go

      > error message:

      > Server: Msg 14278, Level 16, State 1, Procedure sp_verify_jobschedule, Line

      > 177

      > The schedule for this job is invalid (reason: The specified

      > '.sql.todaysummary.com.freq_subday_interval' is invalid.).

      > Is there a rational reason for this? Or, would it be better to stagger two

      > jobs?

      > Thanks

      > Chieko

      >

      #1; Thu, 29 May 2008 01:24:00 GMT
    • The procedure internally uses another system procedure sp_verify_jobschedule

      which has a line like:

      IF ((.sql.todaysummary.com.freq_subday_type <> 0x1) AND (.sql.todaysummary.com.freq_subday_interval < 1))

      OR

      ((.sql.todaysummary.com.freq_subday_type = 0x2) AND (.sql.todaysummary.com.freq_subday_interval < 10))

      BEGIN

      SELECT .sql.todaysummary.com.reason = FORMATMESSAGE(14200, '.sql.todaysummary.com.freq_subday_interval')

      RAISERROR(14278, -1, -1, .sql.todaysummary.com.reason)

      RETURN(1) -- Failure

      END

      This prevents it the job from running at sub-10 second intervals. You could

      conceivably update the proc, but it is not at all recommended. Also note

      that this is undocumented.

      It might be better for you to run multiple jobs carrying out the same

      activity at desired intervals. Also note that smaller the interval, the

      larger could be the load on the server.

      --

      Anith

      #2; Thu, 29 May 2008 01:25:00 GMT
    • Thanks so much for the information.

      I did try to run a stored procedure that had a 'wait for' phrase in it that

      i ran 92 times in a while loop and then put that in a job that ran every 14

      minutes, but I found that during the sql backups and veritas backups, the job

      would take more than 14 minutes and then not restart for another 28 minutes.

      I can try to set the job for a lower frequency so that i won't lose as much

      data, but it sounds like i am over taxing my server with a frequency of less

      than 10 seconds.

      Thanks again for the advise,

      Chieko

      "Anith Sen" wrote:

      > The procedure internally uses another system procedure sp_verify_jobschedule

      > which has a line like:

      > IF ((.sql.todaysummary.com.freq_subday_type <> 0x1) AND (.sql.todaysummary.com.freq_subday_interval < 1))

      > OR

      > ((.sql.todaysummary.com.freq_subday_type = 0x2) AND (.sql.todaysummary.com.freq_subday_interval < 10))

      > BEGIN

      > SELECT .sql.todaysummary.com.reason = FORMATMESSAGE(14200, '.sql.todaysummary.com.freq_subday_interval')

      > RAISERROR(14278, -1, -1, .sql.todaysummary.com.reason)

      > RETURN(1) -- Failure

      > END

      > This prevents it the job from running at sub-10 second intervals. You could

      > conceivably update the proc, but it is not at all recommended. Also note

      > that this is undocumented.

      > It might be better for you to run multiple jobs carrying out the same

      > activity at desired intervals. Also note that smaller the interval, the

      > larger could be the load on the server.

      > --

      > Anith

      >

      >

      #3; Thu, 29 May 2008 01:26:00 GMT