finding active users from sysprocesses

#1

I am trying to find a select on sysprocesses that would list all the active
logins. An active login is a login that has a TSQL statment being executed
on the server,

This didnt work to well! Any ideas. Thanks in advance.

select sp.loginame,
-- more columns
from master..sysprocesses sp
where sp.status not in ('sleeping','background' )
order by 1

#2

You can use the system stored procedure sp_who & sp_who2 for these purposes.

--
- Anith
( Please reply to newsgroups only )

#3

kr (zzb26 (at) email.com) writes:

Quote:

I am trying to find a select on sysprocesses that would list all the
active logins. An active login is a login that has a TSQL statment being
executed on the server,

This didnt work to well! Any ideas. Thanks in advance.

select sp.loginame,
-- more columns
from master..sysprocesses sp
where sp.status not in ('sleeping','background' )
order by 1

In which way did it not work? In any case, you definitely want to add
"or opentrn > 0". A process which has a open transaction is active,
even if it is sleeping.

I have a lock-monitoring routine, and the condition I use is

upper(p.cmd) <> 'AWAITING COMMAND'

I also check whether sysprocesses.blocked > 0.

--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

finding active users from sysprocesses

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

finding active users from sysprocesses - 08-06-2003 , 02:01 PM

Re: finding active users from sysprocesses - 08-06-2003 , 03:02 PM

Re: finding active users from sysprocesses - 08-06-2003 , 04:38 PM