Code to catch long running spids

This code and alterations of it can be used to list spids that have been running for a logn time. Spids up to 50 are system spids so i remove them in the where clause. Many system spids opens up when the service starts and are then running until the service stops. I have here only listed spids running for the sqlagent. I can also remove that part and get all long running queries. If i change days to hours i can narrow it down etc. When checking this for Ramona we found several old spids from their Activepearl application.

SELECT DATEDIFF(day,last_batch,getdate() )AS DaysRunning ,spid,blocked,waittime,db_name(dbid)AS DatabaseName,user_name(uid)AS Username,
physical_io,login_time,last_batch,open_tran,status,program_name,hostprocess,cmd,nt_username,net_library,loginame
FROM master.dbo.sysprocesses (NOLOCK)
WHERE DATEDIFF ( day , last_batch,getdate() ) >1
and spid >50 and program_name like 'sqlagent%'

ORDER BY daysrunning DESC