Greetings!
DBA's need to know when it is safe to stop services or to do other work. It is preferable to do such things when no jobs are running to prevent job failures and data integrity issues. Check out the query below to help.
And don't forget to regularly monitor the 11 VitalSigns used by SQLRx to debug performance bottlenecks.
______________________________________________________________________________ |
 SQL Server Administration: Find the next time that any enabled job will run. Run the query below to return a list of jobs on any SQL Server along with the last run duration and next run time.
USE msdb
GO
SET NOCOUNT ON;
SELECT CONVERT(VARCHAR(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS last_run_duration,
ja.next_scheduled_run_date
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
join msdb.dbo.sysjobs_view j ON ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
AND j.enabled = 1
ORDER BY job_name;
SET NOCOUNT OFF;
GO
____________________________________________________________ |
