0

I am having difficulties trying to find the SPID of a job that is still running over a certain timeframe or has already ran. Does anyone know where I can find that? I do know that I can look at the master.sys.sysprocesses table to find the spid's that are known to SQL Server.

This is one of my many tries: 

SELECT        TOP (100) PERCENT sj.name, sja.run_requested_date, CONVERT(VARCHAR(12), sja.stop_execution_date - sja.start_execution_date, 114) AS Duration, sj.job_id, sj.enabled
FROM            msdb.dbo.sysjobactivity AS sja INNER JOIN
                         msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE        (sja.run_requested_date IS NOT NULL) AND (CONVERT(VARCHAR(12), sja.stop_execution_date - sja.start_execution_date, 114) > CONVERT(VARCHAR(12), '00:00:02:000', 114))
ORDER BY sja.run_requested_date DESC
jjj
  • 1,136
  • 3
  • 18
  • 28
Paul
  • 373
  • 1
  • 5
  • 12

1 Answers1

0

I use Adam Machanic's sp_WhoIsActive to get this info.

For historical data I run it every minute and capture the output to a table. Brent Ozar has an article on how to do this here.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
MillhouseD
  • 171
  • 6