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.
Cisco recommends that you have knowledge of these topics:
Cisco ICM database architecture
Cisco ICM reporting architecture
Microsoft SQL Query Utilities (Query Analyzer for Microsoft SQL Server version 7.0 or 2000 or ISQL_W for Microsoft SQL Server version 6.5)
The information in this document is based on these software and hardware versions:
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.
Refer to Cisco Technical Tips Conventions for more information on document conventions.
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:
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:
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:
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.
There are three methods to end the blocking process:
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:
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.