This SQL returns the scheduler status:

SELECT CASE
WHEN ws_stop_date IS NOT NULL
THEN 'STOPPED'
WHEN ((DATEDIFF(mi,ws_active_date,GETDATE()) - CONVERT(INTEGER,DATEDIFF(mi,ws_active_date,GETDATE())/60)*60) > 15 )
OR (CONVERT(INTEGER,DATEDIFF(mi,ws_active_date,GETDATE())/60)>0)
THEN 'NOT ACTIVE'
WHEN (((DATEDIFF(mi,ws_active_date,GETDATE()) - CONVERT(INTEGER,DATEDIFF(mi,ws_active_date,GETDATE())/60)*60)>((ws_interval/60)+10)
OR CONVERT(INTEGER,DATEDIFF(mi,ws_active_date,GETDATE())/60)>0)
AND ws_poll_flag=1)
THEN 'NOT ACTIVE'
ELSE 'Running'
END
FROM dbo.ws_wrk_scheduler
WHERE ws_name = 'YourSchedulerName'

The procedure sets the status in the metadata.

  • No labels