List of the jobs and when and what time they execute for the next time...
You can use the following script to find that information....
SELECT sj.name AS Name,sjs.next_run_date AS NextRunDate,LEFT(RIGHT('000000' + CAST(sjs.next_run_time AS VARCHAR(6)), 6),2)
+ ':' + SUBSTRING(RIGHT('000000'+ CAST(sjs.next_run_time AS VARCHAR(6)),6), 3, 2)
+ ':' + RIGHT(RIGHT('000000' + CAST(sjs.next_run_time AS VARCHAR(6)),6), 2) AS NextRunTime,
sj.date_created AS DateCreated, sj.date_modified AS DateModified,sj.description AS Description
FROM msdb..sysjobschedules sjs JOIN
msdb..sysjobs sj on sjs.job_id = sj.job_id
--WHERE description not like '%This job is owned by a report%'
ORDER BY 3 --Name
This may be useful to find duration of the last execution.
ReplyDeleteSELECT sj.name,
sja.run_requested_date,
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs sj
ON sja.job_id = sj.job_id
WHERE sja.run_requested_date IS NOT NULL
ORDER BY sja.run_requested_date desc;