Post reply

MSDB - jobservers - last_run_date and last_run_time

  • March 12, 2009 at 12:16 pm

    #384117

    Hi All

    I am trying to get a way to calculate how long it has been since the last time a job ran in hours.

    The date and time in the jobserver table of the MSDB db are 2 separate fields and are int.

    Basically I need (Current Date\Time - Last Run Time\Date)

    I tried several things but can figure it out.

    Any suggestions?

    Thanks

    Craig

  • March 12, 2009 at 12:34 pm

    #958612

    Does this get you what you are looking for?

    select @@servername as ServerName

    , b.name as DatabaseName

    , isnull(str(abs(datediff(HH, getdate(), max(backup_finish_date)))), 999) as HoursSinceLastBackup

    , isnull(convert(char(10), max(backup_finish_date), 101), '01/01/1901') as LastBackupDate

    from

    master.dbo.sysdatabases b left outer join msdb.dbo.backupset a

    on a.database_name = b.name and a.type = 'd'

    group by

    b.name

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • March 12, 2009 at 12:44 pm

    #958618

    MSDB - use the sysjobhistory table...it contains Run_Date, Run_Time and Run_Duration data.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 12, 2009 at 12:48 pm

    #958624

    Ok, I was obviously way to distracted in answering that. Very sorry, thinking you were looking at Backups. Not sure why though... :Whistling: :blush:

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • March 12, 2009 at 3:24 pm

    #958776

    Use following tables from MSDB in your query and it willgive you allthe necessary details.

    sysjobs, sysjobhistory, sysjobschedules, sysjobservers & sysjobsteps...

    ----------------------------------------------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

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