vwWho3 - a filtered vwWho2

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.

USE Admin

GO

IF object_id('dbo.vwWho3') Is Not Null

      DROP VIEW dbo.vwWho3

go

CREATE VIEW [dbo].[vwWho3]

AS

/*    DATE            AUTHOR          REMARKS

      10/15/12          PPaiva          Initial creation.

     

      DESCRIPTION

            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

                  last started.

      NOTE

            If spid = -2, it is an orphaned distributed transaction. 

                  This can be terminated with

                        KILL 'Unit Of Work ID (this is a guid)'

     

      USAGE

            SELECT *

            FROM vwWho3

*/

SELECT *

FROM vwWho2

WHERE Status Not In ('sleeping')

   OR Blocked <> 0

   OR Open_Tran <> 0

   OR spid < 1