Troubleshooting why my WSUS cleanup script was not completing correctly, I decided to see if any stored procedures were still running or hanging. To do this you need to run the following query:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

And then if to stop a long running process simply use this:

kill [session_id]

Enjoy

Jeff