Posts Tagged With: SQL

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.



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


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.


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


Running this for our user ‘BlogDemo’

InputBuffers ‘BlogDemo’

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


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

Author:        John Walker
            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,

/*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
            SPID, [Login], HostName, CPUTime, DiskIO, LastBatch, ProgramName
            ([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,
while @@FETCH_STATUS = 0
      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*/
            SPID = @SPID,
            [Login] = @BufferLogin,
            Hostname = @BufferHostname,
            CPUTime = @BufferCPUTime,
            DiskIO    = @BufferDiskIO,
            LastBatch = @BufferLastBatch,
            ProgramName = @BufferProgramName
            InputBufferID = @@IDENTITY
      fetch next from InputBufferCrs into @SPID, @BufferLogin, @BufferHostname,
                                    @BufferCPUTime, @BufferDiskIO, @BufferLastBatch,

close InputBufferCrs
Deallocate InputBufferCrs

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


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

A Storm is brewing, no wait, its SQL in the cloud

SQL Data Services (SDS) has been available as a cloud service but until now we have been told it is NOT SQL Server in the cloud and is not to be seen as a RDBMS. There has since been a big change to that with the announcement that SDS will be enchanced with TDS, yay another TLA, TDS = Tabular Data Stream, or, a full RBBMS in the cloud.

The SQL Data Services Team blog lists, among other features:

Stored Procedures?…Check




Visual Studio Compatibility?…Check

ADO.Net Compatibility?…Check

ODBC Compatibility?…Check

so there we have it, apparently you can just point your connection string towards the gathering clouds and thats it. Time to run a demo on this i think, coming soon….

The full announcment is available here .

Categories: SDS, SQL, SQL in the cloud, Tabular Data Stream, TDS | Tags: , , , , | Leave a comment

Blog at