July 12, 2004 at 11:23 am
Is there a script that I can use that will show me all the users currently showing activity on any given database. I'd like to find another way rather than looking in the Process Info.
Shane
July 12, 2004 at 12:23 pm
sp_who2
or
select * from master.dbo.sysprocesses
July 13, 2004 at 10:40 am
I prefer using the following SQL that looks at what is happening in a 2 second period. It was posted by someone on this site but I can't remember who, sorry. I have modified it to include the Database name.
use master
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cpu_usage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table cpu_usage
go
select cpu, spid into cpu_usage from sysprocesses
waitfor delay '000:00:2'
select difference = p.cpu - u.cpu, p.cpu, p.loginame, p.spid, p.hostname, d.name as [database], p.last_batch
from sysprocesses p join cpu_usage u on p.spid = u.spid left join sysdatabases d on d.dbid = p.dbid
order by 1 desc
-- Takes snapshot of io usage. waits 2 seconds and compares
drop table cpu_usage
go
select physical_io, spid into cpu_usage from sysprocesses
waitfor delay '000:00:2'
select difference = p.physical_io - u.physical_io,
p.physical_io, p.loginame, p.spid, p.hostname, d.name as [database], p.last_batch
from sysprocesses p join cpu_usage u on p.spid = u.spid left join sysdatabases d on d.dbid = p.dbid
order by 1 desc
drop table cpu_usage
Regards
Peter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply