Filtering sp_who2 by database

An easy way to improve the usability of sp_who2 is to make a database specific version.

The idea is to leverage the information provided by sp_who2, but provide a filter for a given database name. This is really handy on either production or development SQL servers with large numbers of databases.

Lets call our version sp_who2db. The following should be run into your master database.

CREATE PROC [dbo].[sp_who2db] (@DBName VARCHAR(200))
AS
BEGIN

      DECLARE @who2 TABLE(
            [SPID] INT,
            [Status] VARCHAR(200),
            [Login] VARCHAR(200),
            [HostName] VARCHAR(200),
            [BlkBy] VARCHAR(20),
            [DBName] VARCHAR(200),
            [Command] VARCHAR(200),
            [CPUTime] BIGINT,
            [DiskIO] BIGINT,
            [LastBatch] VARCHAR(20),
            [ProgramName] VARCHAR(200),
            [SPID2] INT,
            [RequestID] INT
      )

      INSERT @who2
      EXEC sp_who2

      SELECT * FROM @who2 WHERE DBName = @DBName

END

You can mark it is a system object using the undocumented sp_MS_MarkSystemObject proc:

EXEC sp_MS_MarkSystemObject '[dbo].[sp_who2db]' 

Finally, to use it, just call it as normal:

EXEC sp_who2db 'MyDatabase' 

A similar strategy could be used to filter by blocks, hosts, logins, high CPU or Disk IO etc.

NOTE: The above is tested on MS SQL Server 2008 R2. The columns defined on the @who2 table may need modification for versions other than this, depending on what information sp_who2 provides on those versions.

5 thoughts on “Filtering sp_who2 by database”

  1. Hi there I am so grateful I found your blog page, I really found you by error, while I was
    browsing on Yahoo for something else, Regardless I am here now and would just like to
    say thanks for a remarkable post and a all round exciting blog (I also love the theme/design),
    I don’t have time to browse it all at the minute but I have bookmarked it and also added in your RSS feeds,
    so when I have time I will be back to read a lot more, Please do keep up the fantastic job.

Leave a Reply

Your email address will not be published.

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

This site uses Akismet to reduce spam. Learn how your comment data is processed.