Finding blocking MS-SQL processes

Finding SQL Procedure Blocking Problems 

There may be times that your SQL query will not complete.  You wait, and wait, and wonder what is going on.  It may be that the query is being blocked.

This occurs when one process locks a resource, and another process needs the same resource.  The second process waits for the first to release it.  Normally, this occurs in short order, and the second process then proceeds.

However, there may be times when the first process is waiting for something else.  Sometimes this is a user response that never comes.  It can be that the first process just did not exit properly, and will never release the resource.  In that case, the second process will never proceed, either.

To check for blocking processes, query the sys.sysprocesses table in the msdb database.  Check the column "blocked".  A value other than "0", indicates that the process is being blocked.  The value is the process ID number of the process that is blocking it.  This process ID, is reported in the "spid" column.  

There are other columns that can help determine what is going on.  In particular, the "cmd" column.  Check this column for the spid reported in the "blocked" column.  For example, if your process has a blocked of 72, check the cmd column for the spid of 72.  If it shows "awaiting command", then you most likely have a situation where the first process did not release a resource, and will not finish.  The recourse then is to kill the first process.

To kill the process, do a kill .  In this example, it would be "kill 72".  This kills process 72, rolls back any transactions that it had generated, and releases its resource locks.  Your query can acquire the resources it needs, and proceed.

For Assistance with your SQL problems contact CCS Retail Technical Support.

Leave a Reply