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

Sybase updates...

MySybase Weekly Notification
	Quick Links: Corporate News - Technical Documents - Product Manuals        New Content - Jan 7, 2012 Hello kasi dogga! This notification is sent to you courtesy of MySybase. Here is what is new or updated on the Sybase Web site based on the profiled notifications you have selected.     Corporate News   BBC News: Fantastic futures? Technology and business 2012 BBC News got 12 people from across the technology spectrum to share predictions for 2012. Sybase 365’s Bill Dudley foresees Apple’s iPhone 5 supporting NFC; the Windows Phone holding its own; and BlackBerry thriving in certain geographies and Jan 3, 2012 Computerworld: Mobile campaigns to be hot in 2012 presidential race Social networking campaigns for Republican U.S. presidential candidates are well underway, but mobility is still a largely untapped resource. “Robocalls are so 1990s,” said Sybase 365’s Bill Dudley. “Mobile is a more personal way Jan 4, 2012 DBTA: Sybase Sets Stage for Data Management Growth in 2012 - an Interview with David Jonker Last year was “foundational,” according to Sybase’s David Jonker. He discussed the company’s key product integrations with SAP, which set the stage for 2012, as well as IT trends that loom large in Sybase’s data management s Jan 4, 2012 Mobile Marketing: Apps in the Enterprise The growing number of employees bringing mobile devices to work means employers must take steps toward mobile device management, writes Sybase’s Ian Thain. Companies that delay will find they are too far behind their competitors to catch up. Jan 4, 2012        Technical Documents  update your product list Release Bulletin Replication Server 15.7 for Windows Jan 5, 2012 Release Bulletin Replication Server Data Assurance Option 15.7 Jan 5, 2012 Release Bulletin Sybase Replication Server 15.7 for UNIX and Linux Jan 5, 2012        Product Manuals  update your product list ASE-to-ASE Replication Quick Start Guide Replication Server 15.7 This guide is for Adaptive Server— Enterprise users who want to set up a Replication Server— to replicate data from one Adaptive Server database to another. Jan 5, 2012 Installation Guide Replication Server 15.7 for UNIX This book explains how to set up and configure a replication system, start and stop a Replication Server and upgrade or downgrade Replication Server software. For a complete list of topics, see "Contents". Jan 5, 2012 PowerBuilder 12.5 .NET Features Guide This manual provides an Introduction and information about the GUI, Targets and Projects, DataWindows, Scripts and Code Fundamentals, Database Managment, and more. Jan 5, 2012 Reference Manual Replication Server 15.7 This book describes various Replication Server features. It is for replication system administrators who manage the routine operation of Replication Servers. Jan 5, 2012 Replication Server 15.7 Configuration Guide for Windows This guide is for system administrators or other qualified installers who are familiar with their system environment, networks, disk resources, and media devices. Jan 5, 2012 Replication Server 15.7 Heterogeneous Replication Guide This book introduces heterogeneous replication concepts and addresses the issues peculiar to heterogeneous replication with Sybase replication technology. Jan 5, 2012 Replication Server 15.7 Installation Guide for Windows This book describes how to install Replication Server on the Windows operating system. It contains chapters relating to pre and post-installation tasks, installation of Replication Server, and SySAM licensing information. Jan 5, 2012 Replication Server Data Assurance Option 15.7 Installation Guide This document provides the tasks necessary to complete your product installation. Jan 5, 2012 ASE-to-ASE Replication Quick Start Guide Replication Server 15.5 - Japanese This guide is for Adaptive Server— Enterprise users who want to set up a Replication Server— to replicate data from one Adaptive Server database to another. Jan 6, 2012