Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: sp_help_jobschedule

    Hi I have a problem with sp_help_jobschedule.

    I have a job which is executed very often : every 10 minutes. I check if the job has succeded and with sp_help_jobschedule, i get the next run date. The problem is that this date seems to be updated every 30 minutes. How can i make it updated every 5 minutes ?

    Thanks

    Hachou

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Are you sure that the job is completing in under 10 minutes? If not (say it completes in 21 minutes), then it will schedule itself to run at the next 10 minute interval.

    Check your job history (go into Enterprise Manager, drill down on Management/SQL Agent/Jobs, Right-click on the job in question and select Job history. Check to see how long it's actually taking.

    You might want to check out SQL Agent Mail, too. It can give you hands-off monitoring of job performance.

    Regards,

    hmscott

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    The job is completed in 11 or 12 minutes so less than 20 minutes.

    It is a fast backup. It stores very important datas.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    The schedule will be updated once the job completes, so in your case it should be once in every 12 mins. or so.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    The schedule is updated. That is not the problem.

    I would like to get the time and date of the next run but with 'sp_help_jobschedule' the date and the time are updated every 20 minutes.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If its updating every 20 mins then it seems job is taking the same time to finish, to monitor the event take help of PROFILER and see what sort of activity is taking place.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Feb 2004
    Posts
    4
    I have a Sql job which is executed every ten minutes. The execution takes less than one minute. I want to get the time of the next execution with sp_help_jobschedule but this time is not updated after the execution of the job. It is updated every 20 minutes not every 5 or 6 minutes.

  8. #8
    Join Date
    Dec 2003
    Posts
    31
    i have the same problem, the best solution i've found until now is use the SQLDMO objects
    vb code:

    Dim srv As new SQLDMO.SQLServer2
    Dim qd As SQLDMO.QueryResults2
    Dim jname As String
    Dim i As Long
    Dim NextRunDate As Long
    Dim NextRunTime As Long


    'login the server
    srv.LoginSecure = True
    srv.Connect "."

    Set qd = srv.JobServer.EnumJobs

    For i = 1 To qd.Rows
    jname = qd.GetColumnString(i, 3) 'job name
    If UCase(jname) = UCase(<your job name>) Then
    NextRunDate = qd.GetColumnLong(i, 23)
    NextRunTime = qd.GetColumnLong(i, 24)
    End If
    Next

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •