What's Running
spWhatsRunning does just that. It tells you exactly what is executing on your server. By combining the output of the sp_who and dbcc inputbuffer, this script will tell you exactly whats being executed. DBCC INPUTBUFFER will tell you the same thing, but by the time you get the spid, the offending process may be gone. With spWhatsRunning you get a snapshot of everything.
The 3 parameters are @cpu, @loginame, and @host.
exec master..spWhatsRunning 0, '' to see every process. The parameters are used to filter out specific entries.
use master
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create procedure spWhatsRunning
@cpu integer = 0,
@loginame varchar(60),
@host varchar(50) = null
as
begin
create table #procTable ( EventType varchar(30), Parameters int, EventInfo varchar(255), hostname varchar(255),
program_name varchar(255), loginame varchar(60), cpu decimal(9,4), last_batch datetime,
dbname varchar(60), cmd varchar(60) )
declare @spid integer
declare @input varchar(255)
declare @hostname varchar(255)
declare @program_name varchar(255)
declare @last_batch datetime
declare @dbname varchar(60)
declare @cmd varchar(60)
if @loginame > ''
begin
declare procCurs cursor for select spid, hostname, program_name, loginame, cpu, last_batch, db_name(dbid), cmd
from master.dbo.sysprocesses
where cpu >= ( @cpu * 1000 )
and loginame = @loginame
and hostname = IsNull(@host,hostname)
end
else
begin
declare procCurs cursor for select spid, hostname, program_name, loginame, cpu, last_batch, db_name(dbid), cmd
from master.dbo.sysprocesses
where cpu >= ( @cpu * 1000 )
and hostname = IsNull(@host,hostname)
end
create table #tab ( EventType varchar(30), Parameters int, EventInfo varchar(255) )
open procCurs
fetch next from procCurs into @spid, @hostname, @program_name, @loginame, @cpu, @last_batch, @dbname, @cmd
while @@fetch_status = 0
begin
delete #tab
set @input = 'dbcc inputbuffer(' + convert(varchar(10),@spid) + ') with no_infomsgs'
insert into #tab
exec(@input)
insert into #procTable ( EventType, Parameters, EventInfo, hostname, program_name, loginame, cpu, last_batch, dbname, cmd )
select EventType, Parameters, EventInfo, @hostname, @program_name, @loginame, @cpu / 1000.00, @last_batch, @dbname, @cmd
from #tab
fetch next from procCurs into @spid, @hostname, @program_name, @loginame, @cpu, @last_batch, @dbname, @cmd
end
close procCurs
deallocate procCurs
select * from #procTable order by EventInfo desc
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
