Friday, 26 July 2019

Find the run-time status for all SQL Server Agent jobs

Use below script to Find the run-time status for all SQL Server Agent jobs

use msdb
go



SELECT
        sj.Name,
                 CASE
                      WHEN sja.start_execution_date IS NULL THEN 'Not running'
                      WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
                      WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
                      END  AS 'RunStatus'
FROM   msdb.dbo.sysjobs sj
JOIN   msdb.dbo.sysjobactivity sja
ON     sj.job_id = sja.job_id
WHERE  sja.session_id =
                    (SELECT MAX (session_id) FROM msdb.dbo.sysjobactivity)
order by RunStatus desc

No comments:

Post a Comment

Disable CDC at table level

 How to Disable CDC( Change Data Capture) for tables Change Data Capture (cdc) property is disabled as default.  here I will Query you how t...