Posts Tagged With: Tools

sp_who2 and dbcc inputbuffer mashup

One thing I find my self doing from time to time is looking up what a particular user or set of users are doing. To do this i would run sp_who2, pick out the SPID of the user concerned and then run dbcc InputBuffer(@SPID) for each of the interesting results to see what was the command last run, or as is often the case, which command is currently running.

After doing this a couple of times i thought, why and i repeating this action, so i created InputBuffers, a stored procedure to help me filter out some noise and see what activity the users are up to

Complete code listing at the bottom

Initially i have created the procedure to accept two filters, @Login and @Hostname, which are both varchar(128). I am planning to extend this to also include Database name, program name and also top (n) CPU or DiskIO hogs.

The sp_who2 results are loaded into a temporary table which we then use as a basis for a cursor to call dbcc inputbuffer(SPID), this is what retrieves the last command.

image

image

One slight issue is that the DBCC command return does not contain the identifying information from sp_who.

image

To get around this, the table variable used to stored the sp_who2 results has a identity field which is retrieved with @@Identity, then the new entry in the results table variable is updated with the cursors current row.

image

From here we just need to output the results and we are done.

image

Running this for our user ‘BlogDemo’

InputBuffers ‘BlogDemo’

allows us to cut through the noise and just see the activity from BlogDemo

image

Feel free to use the code listing below and also please add comments if there are any additions you would like made, or, anything that you think can be changed to make it better.


Code Listing:


 

 

CREATE PROCEDURE [dbo].[InputBuffers]
    @Login varchar(128) = null,
    @Hostname varchar(128) = null
AS

/**************************************************************
Author:        John Walker
                   sqljohn.wordpress.com
            twitter: sqljohn
Date:        13/09/2010
Version:    1.0
***************************************************************
InputBuffers is a mash up of sp_who2 and dbcc inputbuffer(), it returns
the last statement executed by a user, as dbcc input buffer does
but it lets you filter by login or hostname.
This returns not only the statement but also the login, hostname and SPID
**************************************************************/

/*table to hold the sp_who2 results*/
declare @sp_who2 table
(SPID       int,
[Status]    varchar(30) not null,
[Login]           varchar(128) not null,
[HostName]  varchar(128) not null,
BlkBy       varchar(5) not null,
DBName            varchar(128) null,
Command           varchar(16) not null,
CPUTime           INT not null,
DiskIO            INT not null,
LastBatch   varchar(20) not null,
ProgramName varchar(128) not null,
rightSPID   int,
REQUESTID   int)

/*output table to hold inputBuffer statement and user info*/
declare @InputBuffer table
(
InputBufferID   int identity(1,1),
EventType       varchar(30),
[Parameters]    int,
EventInfo       varchar(4000),
SPID            int,
[Login]         varchar(128),
Hostname        varchar(128),
CPUTime         INT,
DiskIO          INT,
LastBatch        varchar(20),
ProgramName        varchar(128)
)

/*load table var ewith results of sp_who2*/
INSERT @sp_who2 EXEC sp_who2

/*cycle through the filtered results loading the result table variable*/
declare @SPID                as int
declare @BufferLogin        as varchar(128)
declare @BufferHostname        as varchar(128)
declare @BufferCPUTime        as INT
declare @BufferDiskIO        as INT
declare @BufferLastBatch    as varchar(20)
declare @BufferProgramName    as varchar(128)

declare InputBufferCrs cursor for
      select
            SPID, [Login], HostName, CPUTime, DiskIO, LastBatch, ProgramName
      from
            @sp_who2
      where
            ([Login] = @Login or @Login is null)
            and (hostname = @Hostname or @Hostname is null)

open InputBufferCrs
fetch next from InputBufferCrs into @SPID, @BufferLogin, @BufferHostname,
                                    @BufferCPUTime, @BufferDiskIO, @BufferLastBatch,
                                    @BufferProgramName
while @@FETCH_STATUS = 0
begin
      insert @InputBuffer (EventType, Parameters, EventInfo)
      exec (‘DBCC InputBuffer(‘ + @SPID + ‘) WITH NO_INFOMSGS’)
      /*we cant load the user info during the exec of dbcc so update them here
           with the most recent @@identity*/
      update
            @InputBuffer
      set
            SPID = @SPID,
            [Login] = @BufferLogin,
            Hostname = @BufferHostname,
            CPUTime = @BufferCPUTime,
            DiskIO    = @BufferDiskIO,
            LastBatch = @BufferLastBatch,
            ProgramName = @BufferProgramName
      where
            InputBufferID = @@IDENTITY
      fetch next from InputBufferCrs into @SPID, @BufferLogin, @BufferHostname,
                                    @BufferCPUTime, @BufferDiskIO, @BufferLastBatch,
                                    @BufferProgramName
end

close InputBufferCrs
Deallocate InputBufferCrs

/*output our results*/
select
      SPID, [Login], Hostname, EventType, Parameters, EventInfo,
      CPUTime, DiskIO, LastBatch, ProgramName
from
      @InputBuffer
order by
      Hostname, Login

GO

Categories: SQL Tools | Tags: , , , | 1 Comment

Create a free website or blog at WordPress.com.