Do you often need to find out what is being processed on your server?
How long each process has been running, what the command was, what is blocking, how much i/o it is causing?
This stored procedure will display the last command executed and other attributes for all spids or those with a given status.
The default status is 'runnable' as that is the usually requirement.
By calling the procedure with 'all' all spid are displayed.
By calling the proceduer with a spid just that spid is displayed.
By calling the proceduer with a 'blk' all blocked and blocking spids are displayed.
To keep a record or what is happenning on your server output the result of this procedure to a table and schedule the call.
Note: Sometimes the status displayed is not that requested due to the delay between getting the list of spids and the delay.
Sample output
blk spid physical_io status last_batch cmd command login_time HostName
---- ---- -------------------- ------------ ----------------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------
1 0 background 2003-06-06 20:06:36.180 LAZY WRITER 2003-06-06 20:06:36.180
10 0 background 2003-06-06 20:06:36.180 TASK MANAGER NULL 2003-06-06 20:06:36.180
11 0 background 2003-06-06 20:06:36.180 TASK MANAGER NULL 2003-06-06 20:06:36.180
12 0 background 2003-06-06 20:06:36.180 TASK MANAGER NULL 2003-06-06 20:06:36.180
13 0 background 2003-06-06 20:06:36.180 TASK MANAGER NULL 2003-06-06 20:06:36.180
3 0 background 2003-06-06 20:06:36.180 SIGNAL HANDLER 2003-06-06 20:06:36.180
4 0 background 2003-06-06 20:06:36.180 LOCK MONITOR 2003-06-06 20:06:36.180
5 1 background 2003-06-06 20:06:36.180 TASK MANAGER NULL 2003-06-06 20:06:36.180
6 0 background 2003-06-06 20:06:36.180 TASK MANAGER NULL 2003-06-06 20:06:36.180
8 0 background 2003-06-06 20:06:36.180 TASK MANAGER NULL 2003-06-06 20:06:36.180
9 0 background 2003-06-06 20:06:36.180 TASK MANAGER NULL 2003-06-06 20:06:36.180
54 21 runnable 2003-06-07 00:11:05.390 SELECT exec sp_nrSpidByStatus 'all' -- all spids|||| 2003-06-07 00:10:40.227 LAPTOP8200
2 0 sleeping 2003-06-06 20:06:36.180 LOG WRITER 2003-06-06 20:06:36.180
52 23 sleeping 2003-06-07 00:05:23.440 AWAITING COMMAND test..sp_sproc_columns;1 2003-06-07 00:05:05.413 LAPTOP8200
53 0 sleeping 2003-06-07 00:10:10.573 AWAITING COMMAND use [test] 2003-06-07 00:10:00.070 LAPTOP8200
7 0 sleeping 2003-06-06 20:06:36.180 CHECKPOINT SLEEP 2003-06-06 20:06:36.180
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_nrSpidByStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_nrSpidByStatus]
GO
Create procedure sp_nrSpidByStatus
@status varchar(20) = 'runnable'
as
/*
exec sp_nrSpidByStatus -- all spids whith status runnable
exec sp_nrSpidByStatus 'sleeping' -- all spids whith status sleeping
exec sp_nrSpidByStatus 'background' -- all spids whith status background
exec sp_nrSpidByStatus 'sleeping' -- all spids whith status sleeping
exec sp_nrSpidByStatus 'all' -- all spids
exec sp_nrSpidByStatus 'blk' -- all blocked or blocking spids
exec sp_nrSpidByStatus '74' -- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 56
*/
set transaction isolation level read uncommitted
set nocount on
declare @cmd varchar(1000)
declare @buf varchar(1000) ,
@id int ,
@spid int ,
@maxSpid int
create table #spid (spid int, command varchar(1000) null)
create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
create table #spids (spid int)
if isnumeric(@status) = 1
begin
insert #spids select @status
end
else if @status = 'blk'
begin
insert #spids
select spid from master..sysprocesses where blocked <> 0
union
select blocked from master..sysprocesses where blocked <> 0
end
else
begin
insert #spids select spid from master..sysprocesses where (status = @status or @status = 'all') and ecid = 0
end
select @spid = 0 ,
@maxSpid = max(spid)
from #spids
while @spid < @maxSpid
begin
select @spid = min(spid) from #spids where spid > @spid
select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'
delete #temp
insert #temp
exec (@cmd)
select @id = 0 ,
@buf = ''
select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')
from #temp
insert #spid
select @spid, @buf
end
select blk = case when s.blocked <> 0 then convert(varchar(3),s.blocked) else ' ' end ,
spid = convert(varchar(4),#spid.spid) ,
s.physical_io ,
status = left(s.status,12) ,
last_batch = convert(varchar(23),s.last_batch,121) ,
s.cmd ,
#spid.command ,
login_time = convert(varchar(23),s.login_time,121) ,
s.HostName
from #spid ,
master..sysprocesses s
where s.spid = #spid.spid
and (ecid = 0 or isnumeric(@status) = 1)
order by s.status, #spid.spid
drop table #spid
drop table #temp
go