Simple TSQL script to see status of active processes

create procedure sp__who2 as

select spid , fid ,

substring(suser_name(suid),1,10) Login_name,

substring(program_name,1,10) Program ,

status , cmd ,

substring(db_name(dbid),1,19) db_name,

object_name(id, dbid) objectname,

substring(tran_name,1,22) tran_name,

blocked, time_blocked, physical_io

from master..sysprocesses

go

More info about the system table sysprocesses:

sysprocesses contains information about Adaptive Server processes, but it is not a normal table. It is built dynamically when queried by a user. No updates to sysprocesses are allowed. Use the kill statement to kill a process.

Columns

The columns for sysprocesses are:

Name	Datatype	Description
spid	smallint	Process ID.
kpid	int	Kernel process ID.
enginenum	int	Number of engine on which process is being executed.
status	char(12)	Process ID status (see below table).
suid	int	Server user ID of user who issued command.
hostname	varchar(30) null	Name of host computer.
program_name	varchar(30) null	Name of front-end module.
hostprocess	varchar(30) null	Host process ID number..
cmd	varchar(30) null	Command or process currently being executed. Evaluation of a conditional statement, such as an if or while loop, returns cond.
cpu	int	Cumulative CPU time for process in ticks
physical_io	int	Number of disk reads and writes for current command.
memusage	int	Amount of memory allocated to process.
blocked	smallint	Process ID of blocking process, if any.
dbid	smallint	Database ID.
uid	int	ID of user who executed command.
gid	int	Group ID of user who executed command.
tran_name	varchar(64) null	Name of the active transaction.
time_blocked	int null	Time blocked in seconds.
network_pktsz	int null	Current connection’s network packet size.
fid	smallint null	Process ID of the worker process’ parent.
execlass	varchar(30) null	Execution class that the process is bound to.
priority	varchar(10) null	Base priority associated with the process.
affinity	varchar(30) null	Name of the engine to which the process has affinity.
id	int null	Object ID of the currently running procedure (or 0 if no procedure is running).
stmtnum	int null	The current statement number within the running procedure (or the SQL batch statement number if no procedure is running).
linenum	int null	The line number of the current statement within the running stored procedure (or the line number of the current SQL batch statement if no procedure is running).
origsuid	int null	Original server user ID. If this value is not NULL, a user with an suid of origsuid executed set proxy or set session authorization to impersonate the user who executed the command.
block_xloid	int null	Unique lock owner ID of a lock that is blocking a transaction.
clientname	varchar(30) null	Optional – name by which the user is know for the current session. Adaptive Server automatically stores one or more spaces in clientname, clienthostname, and clientapplname columns. For this reason, a query using any of these three columns that includes “is null” does not return an expected result set.
clienthostname	varchar(30) null	Optional – name by which the host is known for the current session.
clientapplname	varchar(30) null	Optional – name by which the application is known for the current session.
sys_id	smallint null	Unique identity of companion node.
ses_id	int null	Unique identity of each client session.
loggedindatetime	datetime null	Shows the time and date when the client connected to Adaptive Server. See “Row-level access control“ in Chapter 11, “Managing User Permissions” of the System Administration Guide for more information.
ipaddr	varchar(64) null	IP address of the client where the login is made. See “Row-level access control“ in Chapter 11, “Managing User Permissions” of the System Administration Guide for more information.
nodeid	tinyint null	Reserved for future use.

Values for the status column:

Table 1-19: sysprocesses status column values
Status	Meaning
alarm sleep	Waiting for alarm to wake process up (user executed a waitfor delay command)
background	A process, such as a threshold procedure, run by Adaptive Server rather than by a user process
infected	Server has detected a serious error condition; extremely rare
latch sleep	Waiting on a latch acquisition
lock sleep	Waiting on a lock acquisition
PLC sleep	Waiting to access a user log cache
recv sleep	Waiting on a network read
remote i/o	Performing I/O with a remote server
runnable	In the queue of runnable processes
running	Actively running on one of the server engines
send sleep	Waiting on a network send
sleeping	Waiting on a disk I/O, or some other resource (often indicates a process that is running, but doing extensive disk I/O)
stopped	Stopped process
sync sleep	Waiting on a synchronization message from another process in the family