In the last post we created a view that is the contents of sp_who2, plus some other valuable information.
Here we will create vwWho3, which is the same as vwWho2, with a filter to see only active or pertinent spids.
IF object_id('dbo.vwWho3') Is Not Null
DROP VIEW dbo.vwWho3
CREATE VIEW [dbo].[vwWho3]
/* DATE AUTHOR REMARKS
10/15/12 PPaiva Initial creation.
Same as vwWho2 except this only gets active processes, plus
one row from spids between 1 and 50, so that it is easy
to show the date/time this instance of SQL Server was
If spid = -2, it is an orphaned distributed transaction.
This can be terminated with
KILL 'Unit Of Work ID (this is a guid)'
WHERE Status Not In ('sleeping')
OR Blocked <> 0
OR Open_Tran <> 0
OR spid < 1