This document
provides a method to identify and remove Microsoft SQL processes that block
other processes in the Cisco Intelligent Contact Management (ICM) system. It is
important to identify the SQL processes that block other processes because it
promotes the illusion that the other processes are hung. In fact, the processes
are not hung, but are simply waiting for resources to be released by the
blocking process.

The
information in this document is based on these software and hardware
versions:

Cisco ICM version 4.6.2 or
later

Microsoft SQL Server version 6.5 or
later

The information in this document
was created from the devices in a specific lab environment. All of the devices
used in this document started with a cleared (default) configuration. If your
network is live, make sure that you understand the potential impact of any
command.

In order to determine if a SQL process blocks others, the
sp_who stored command needs to be run with Query
Analyzer (Microsoft SQL Server version 7.0 or 2000) or ISQL_W (Microsoft SQL
version 6.5).

This shows a hypothetical result when the
sp_who command is run.

Figure 1: sp_who

If a process has a value in the BlkBy column, this indicates the process
is blocked by a process whose Server Process ID (SPID) is that value. In this
example, the process with SPID 9 blocks the process with SPID 13. Therefore, it
keeps that process from completing.

Once you have determined which process
is blocked and which process is blocking, the next step is to determine the
source of the blocking process so that it can be terminated.

Although it is possible to use the HostName and Login columns in the
sp_who results window in order to diagnose the source of a blocking process, in
most cases this might not provide all the necessary details of what the process
does. One possible method to obtain this information is to go to the machine
that is the source of the blocking process and see if there is a hung ISQL_W,
Query Analyzer or Enterprise Manager session.

An alternative
option is to look at part of the query that the process runs in order to obtain
additional identifying information. Issue this command:

dbcc
inputbuffer(<x>)

In
the preceding command syntax, x indicates the SPID of the
blocking process. If you use the data from the
sp_who command shown in Figure 1, x equals 9. This shows a
hypothetical result when the dbcc inputbuffer
command is run.

Figure 2: dbcc
inputbuffer

Though the query can be displayed with the dbcc
inputbuffer command, the intended purpose of this query might
still be unknown. If this is the case, and if the HostName is not clearly
identified in the sp_who command results, issue
these standard SQL and DOS networking commands in order to identify the IP
address and hostname of the source.

Issue
this SQL query on the blocking process SPID in order to obtain the MAC address
of the machine:

select * from master.dbo.sysprocesses where
spid = <y>

In this query,
y indicates the SPID of the blocking process and is
replaced with 9 (see Figure 2).

The
net_address column contains the MAC address of the machine that corresponds to
the specified SPID.

Issue this DOS
networking command in a DOS window in order to determine the IP address that
corresponds to this MAC address:

arp
-a

This command displays the IP address of all
connected machines.

Match up the net_address
value from the sysprocesses query (step 1) with the corresponding Physical
Address in the arp -a results (step 2). This
determines if the IP address that corresponds to the Physical Address is the IP
address of the machine that runs the blocking process. Issue this DOS
networking command in a DOS window in order to find the corresponding hostname
of this machine:

ping -a
<IPaddress>

In this
command, <IPaddress>
indicates the IP address identified with the arp -a
command.

When you have the IP address and/or hostname of the
machine that runs the blocking process, it is easier to track down the root
problem of the block.

Approach the machine that runs the blocking
process and determine the application that connects to the Microsoft SQL
Server. If the application can be shut down, close it.

Note: If
you close the application, it might take some time. This depends on the nature
of the work done by the application. This process also triggers the Microsoft
SQL Server to perform a rollback of any uncommitted work started by the
application. This is the safest method to end the
offending process.

Kill the
blocking process with this SQL command:

kill
<z>

In this command,
<z> is the SPID of the
blocking process.

Caution: The
kill command is a very
dangerous approach to clear the blocking process because it can
potentially corrupt the SQL database if the blocking process performs a
database update. The kill command can also take
quite a while to complete if the blocking process has performed a lot of work
that the kill process must undo (rollback). Refer to
SQL Books online for more information on the kill
command. This should be included with the Microsoft SQL Server installation
media.

The final method to end the
blocking process is to reboot the machine that runs the blocking
process.

Note: As with the kill
command option, this is a very dangerous approach to clear
the blocking process because the application that is connected to the Microsoft
SQL Server is not shut down cleanly.