• SQLRNNR (3/11/2013)


    Jeff Moden (3/11/2013)


    David Burrows (3/11/2013)


    SELECT DISTINCT j.name as job_name,

    msdb.dbo.agent_datetime(run_date, run_time) AS [run_datetime],

    DATEADD(second,DATEDIFF(second,0,msdb.dbo.agent_datetime(19000101, run_duration)),msdb.dbo.agent_datetime(run_date, run_time)) AS [run_endtime]

    FROM msdb..sysjobhistory h

    INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id

    WHERE j.name NOT LIKE 'Backup%'

    AND j.name NOT LIKE 'Cleanup%'

    AND j.name NOT LIKE 'Shrink%'

    AND j.name <> 'WMI_Disk_Space_Notification'

    AND j.name <> 'syspolicy_purge_history'

    ORDER BY run_endtime DESC

    Note that this uses an undocumented system function

    Thank you Mr. Burrows. I learned something new. In the past, I've written just such a function that apparently works the same way. I don't need to do that anymore.

    Very nice indeed. I learned something new as well.

    Very nice. 😎 I tried adding the formatted duration but I can't quite get it.

    Thank you.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/