About Me

Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present. 517 Lafarge Ave. Louisville, CO 80027 7667 Dyer Road Louisville, CO 80027

Monday, October 15, 2012

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

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
                  last started.

            If spid = -2, it is an orphaned distributed transaction. 
                  This can be terminated with
                        KILL 'Unit Of Work ID (this is a guid)'
            SELECT *
            FROM vwWho3


FROM vwWho2
WHERE Status Not In ('sleeping')
   OR Blocked <> 0
   OR Open_Tran <> 0
   OR spid < 1

No comments:

Post a Comment