April 26, 2005 at 5:07 pm
How can I run to see what's the bufferinput when running sp_who2 ?
Any Ideas?
April 27, 2005 at 12:12 am
Try DBCC INPUTBUFFER.
Also see this thread.
April 28, 2005 at 2:13 am
dbcc inputbuffer (), where is the spid from sp_who2. You could write a procedure to use a view of the sysprocesses table and wrap a cursor around each spid returned from the view...
April 28, 2005 at 11:20 am
If Object_Id('TempDB..#Who') is not Null Drop Table #Who
If Object_Id('TempDB..#DBCC') is not Null Drop Table #DBCC
Create Table #Who
(
SPID1 Int,Status VarChar(64),Login VarChar(64),HostName VarChar(64),
BlkBy VarChar(64),DBName VarChar(64),Command VarChar(64),CPUTime Int,
DiskIO Int,LastBatch VarChar(64),ProgramName VarChar(64),SPID2 Int,
[InputBuffer] nVarChar(256)
)
Create Table #DBCC (EventType VarChar(64),Parameters Int,EventInfo nVarChar(256))
Insert dbo.#Who
(
SPID1,Status,Login,HostName,BlkBy,DBName,Command,
CPUTime,DiskIO,LastBatch,ProgramName,SPID2
)
Exec sp_who2
Declare @SPId Int,@SQL VarChar(256),@IB nVarChar(256)
Declare Csr Cursor for Select SPId1 from dbo.#Who Open Csr
Fetch Next from Csr into @SPId
While @@Fetch_Status=0 Begin
Truncate Table dbo.#DBCC
Set @SQL='DBCC InputBuffer('+Cast(@SPId as VarChar)+')'
Insert dbo.#DBCC Exec(@SQL)
Set @ib=Case @@RowCount when 1 then (Select IsNull(EventInfo,'') from #DBCC) else N'' End
Update dbo.#Tmp set [InputBuffer]=@IB where SPID1 = @SPId
Fetch Next from Csr into @SPId
End
Close Csr Deallocate Csr
Select * from dbo.#Tmp
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply