| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

Transactional Replication Monitoring - SQL Job Perspective - Last Run Time and Run Status
by BF (Principal Consultant; Architecture; Engineering)

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 as 'REPL Job Category'
, 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 desc, asc