This script will check for long running SPIDS and mail the SPID number, duration,
username and the SQL being executed.
It can be run ad-hoc or run as a scheduled job, or you could make a stored procedure out of it.
It reports on the longest running SPID in the instance with an execution time greater than 2 minutes
It will email an HTML formatted table.
The script uses a table called master.dbo.LongRunningQueries, but you can use a
temp table or put this table wherever you want.
This has been tested on SQL 2008 R2 but should work on any version of 2008, and probably 2005 as well.
As it stands it can be pasted into a query window or job and run.