Gokul's Blog

Debugging with SP_who & Dbcc Inputbuffer (SPID)

Leave a comment

When dealing with the problem of connection leaks SP_who and SP_who2 are very helpful to find the issue.

With some googling and writing some code was able to create some code which will help us to determine the problem causing procedure, functions, etc.

drop table #retTable
drop table #DbccOutput

create table #retTable(
    spid smallint,
    ecid smallint,
    status nchar(30),
    loginname nchar(128),
    hostname nchar(128),
    blk char(5),
    dbname nchar(128),
    cmd nchar(16),
    request_id INT
)

INSERT INTO #retTable EXEC sp_who
SELECT * FROM #retTable WHERE dbname = ‘MyTableName’

declare @iSpidMax varchar(6)
declare @sSql varchar(50)

Create Table #DbccOutput ( spid integer, EventInfo varchar(256) )
Create Table #DbccOutputTemp ( EventType varchar(256), Parameters integer, EventInfo varchar(256))
Declare MyCursor Cursor For
  Select Distinct spid From #retTable
Open MyCursor
Fetch Next From MyCursor Into @iSpidMax
While @@Fetch_Status = 0
Begin
  Set @sSql = ‘DBCC inputbuffer(‘ + Convert( varchar(3), @iSpidMax ) + ‘)’
  Insert Into #DbccOutputTemp Exec ( @sSql )
  Insert Into #DbccOutput ( spid, EventInfo )
    Select @iSpidMax As spid, EventInfo From #DbccOutputTemp
  Delete #DbccOutputTemp
  Fetch Next From MyCursor Into @iSpidMax
End
Close MyCursor
Deallocate MyCursor

select * from #DbccOutput
select distinct eventinfo from #DbccOutput

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s