Presents your 
SQL SERVER E-NEWSLETTER for June 26, 2003

<------------------------------------------->

GET INFORMATION ABOUT A SPECIFIC TRANSACTION OR PROCESS USING SP_WHO

A user's transactions or process can sometimes throw a wrench into your
SQL Server 2000 transactions. For instance, when one user creates an
exclusive lock on data, it can create a bottleneck, as other users wait until
the data is free to continue their transactions. You won't hear from the
creator of the lock, but you'll probably get complaints from the users
who have to wait. That's when it's time to determine what process is
creating the problem.

When you're searching for the process that's slowing down your users,
sp_who will come in handy. Sp_who is a system-stored procedure that can
provide current information about users and processes. In addition, it can
be used to return information on active processes, excluding processes
that are idle.

The result set of sp_who offers a number of valuable columns, which
include: spid (the system process ID), ecid (the context ID of a given thread
associated with the spid), process status, loginame, hostname, blk (the
system ID for a blocking process, if one exists), the database name, and
the SQL Server command executing the process.

The following example uses sp_who to list all of the current processes
that are running:

USE master
EXEC sp_who

Your result set can look similar to the one below:

spid     ecid    status       loginame     hostname     blk     dbname    
cmd 
----     ----    ------       --------      --------     ---     ------    
--- 
1        0       background   sa                        0       pubs      
LAZY WRITER          
2        0       sleeping     sa                        0       pubs      
LOG WRITER          
3        0       background   sa                        0       master    
SIGNAL HANDLER


Etc.

51        0        runnable        DOMAIN\loginX     serverX    0     
Nwind   BACKUP DATABASE      
51        2        runnable        DOMAIN\loginX     serverX    0     
Nwind   BACKUP DATABASE      
51        1        runnable        DOMAIN\loginX     serverX    0     
Nwind   BACKUP DATABASE      
52        0        sleeping        DOMAIN\loginX      serverX    0    
master AWAITING COMMAND     
53        0        runnable       DOMAIN\loginX      serverX    0     
pubs       SELECT               
(16 row(s) affected)

If you want to find out about a specific user's process, you can use 
his
or her login, as in the example below:

USE master
EXEC sp_who 'jharvey'

Other options include getting information about all active processes or
a specific process using a process ID, as in the following examples:

USE master
EXEC sp_who 'active'

USE master
EXEC sp_who '10'  -- 10 is the specific process id

J.E. Harvey, MCSD, MCDBA, has been tinkering, writing, and consulting
about technology for more than two decades.

----------------------------------------