MS SQL Server - Spid checker query help

Author

Spid checker query help

fnadal

2006-11-16, 7:12 pm

I have this query that I use to check "behind the scenes" on all the
processes on the Database. However it doesn't show me the actual spid in the
results. My problem is the 'insert into #tmp EXEC (@strSQL)' line. If I
insert a print @spid inside the cursor, it prints the spids in the messages
window and I can't see what spid goes with what result.

For example, right now the query results are like this, the result of a dbcc
inputbuffer statement:

rpc event parameters eventinfo

I want it to look like this:

spid rpcevent parameters eventinfo

It's probably a simple change but I'm stumped. Thanks in advance!!

create table #tmp
(eventtype varchar(100),
parameters int,
eventinfo varchar(255))

DECLARE @strSQL NVARCHAR(255)

DECLARE
@spid int

DECLARE spid_CURSOR INSENSITIVE SCROLL CURSOR
FOR
SELECT spid
from master..sysprocesses where kpid > 0
--FOR READ ONLY

OPEN spid_CURSOR
FETCH FIRST
FROM spid_CURSOR
INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = 'dbcc inputbuffer('+ CAST(@spid AS CHAR (3)) +')'
insert into #tmp EXEC (@strSQL)

FETCH NEXT
FROM spid_CURSOR
INTO @spid
END

close spid_CURSOR
deallocate spid_CURSOR
select * from #tmp
drop table #tmp

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200611/1

Paul Ibison

2006-11-16, 7:12 pm

In SQL Server 2000, you could store the spids in a separate temp table (from
SELECT spid from master..sysprocesses where kpid > 0) and associate these
values with the dbcc inputbuffer results based on order. If you're on SQL
Server 2005 there are other methods of doing this (below).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

SELECT st.text as [Command text],

login_time,

[host_name],

program_name,

sys.dm_exec_requests.session_id,

client_net_address,

sys.dm_exec_requests.status,

command,

db_name(database_id)
as DatabaseName

FROM sys.dm_exec_requests

inner join sys.dm_exec_connections on sys.dm_exec_requests.session_id =
sys.dm_exec_connections.session_id

inner join sys.dm_exec_sessions on sys.dm_exec_sessions.session_id =
sys.dm_exec_requests.session_id

cross apply sys. dm_exec_sql_text(sql
_handle) AS st

WHERE sys.dm_exec_requests.session_id >= 51

GO

Sponsored Links