August 2010 Tip of the Month

By SQLRx Admin | Helpful Scripts

Aug 06
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.

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

About the Author

>