This query returns all Replication SQL Jobs and reports on it's Last Run Time and Run Status.
Transactional Replication Monitoring - SQL Job Perspective - Status & Last Run Time
;WITH CTE_MostRecentReplicationSQLJobRun AS
(
SELECT job_id, step_name, run_status, msdb.dbo.agent_datetime(run_date, run_time) [Time Run]
,RANK() OVER (PARTITION BY job_id ORDER BY instance_id DESC) AS rnk
FROM [msdb].[dbo].sysjobhistory WHERE step_name='Run agent.' and job_id in (Select job_id from [msdb].[dbo].sysjobs where category_id in (10,11,12,13,14,15,16,17,18,19))
)
SELECT
c.name as 'REPL Job Category'
,sj.name as 'REPL Job Name'
,CASE run_status when 0 Then 'Failed' When 1 Then 'Succeeded' When 2 Then 'Retry' When 3 Then 'Canceled' When 4 Then 'In Progress' End As 'Run Status'
,[Time Run] as 'Last Time Run'
,mrjr.Step_Name as 'Job Step'
,CASE WHEN enabled=1 THEN 'Enabled' ELSE 'Disabled' END 'Job Status'
FROM CTE_MostRecentReplicationSQLJobRun mrjr
JOIN [msdb].[dbo].sysjobs sj ON mrjr.job_id=sj.job_id
JOIN [msdb].[dbo].[syscategories] c ON sj.category_id = c.category_id
WHERE rnk=1
ORDER BY c.name desc, sj.name asc
|
|
|
|
|
|