Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Deon Benjamin,
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 minutesIt will email an HTML formatted table. The script uses a table called master.dbo.LongRunningQueries, but you can use atemp 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.