I picked up a good tip from one of our DBA's yesterday. He uses sp_who2 to see which users have active threads/processes running against a particular database. This stored proc returns the PIDs as well (Process ID number). I had known about this previously. What was new to me was he also used:
to get the database to tell him which stored proc was being executed by that PID. That seems to me like a handy trick. It certainly solved the problem I was having.