Post reply

is it possible to run a query for all SQL jobs that ran at a specific time?

  • April 13, 2012 at 2:22 am

    #392013

    i'm trying to find out why a SQL job has started failing. the error is 'could not complete cursor operation because the table schema changed after the cursor was declared'.

    i believe this could be caused by anything from adding Indexes to shrinking the database at the same time.

    I feel that being able to narrow down my search on what jobs were running at the same time as the fail, would be the first step in figuring out the problem.

    thanks for the help

  • April 13, 2012 at 3:05 am

    #1473099

    dbo.sysjobhistory

    dbo.sysjobs

    These tables in msdb contain all job information, join them on job_id.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • April 13, 2012 at 4:02 am

    #1473113

    brilliant, thanks for the info.

    i also used this thread http://www.sqlservercentral.com/Forums/Topic542581-145-1.aspx, based on the table names you gave me, and have found the offending SQL job.

    select (select name from sysjobs where job_id = y.job_id),* from (

    SELECT step_id, job_id, run_time

    , run_time/10000 AS run_time_hours

    , (run_time%10000)/100 AS run_time_minutes

    , (run_time%10000)%100 AS run_time_seconds

    , (run_time/10000 /*run_time_hours*/ * 60 * 60 /* hours to minutes to seconds*/)

    + ((run_time%10000)/100 /* run_time_minutes */ * 60 /* minutes to seconds */ )

    + (run_time%10000)%100 AS run_time_elapsed_seconds

    , CONVERT(DATETIME, RTRIM(run_date)) AS Start_Date

    , CONVERT(DATETIME, RTRIM(run_date)) +

    ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime

    , ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) as ElapsedSeconds

    , CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)

    + ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime

    FROM msdb.dbo.sysjobhistory

    )y

    where y.End_DateTime > '2012-04-13 06:27:00.000'

    --and y.End_DateTime < '2012-04-13 06:45:00.000'

    and Start_DateTime < '2012-04-13 06:27:00.000'

    and not step_id = 0

    order by y.End_DateTime

Viewing 3 posts - 1 through 2 (of 2 total)

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