SQL Server SysProcesses And Blocking Processes
You can use sysprocesses to identify which processes are blocking other processes running on the SQL Server.
All processes locks tables and then releases those locks.
Locks in a way helps to maintain the data integrity by preventing problems that would arise if more than one processes are changing data on a table at the same time.
Locks usually do not cause problems affecting the normal work of the SQL Server.
But when a process is holding a lock long enough to block other processes that want to retrieve the data or modify the data locked than the locking problems arise.
Processes that are blocked by the locking process will not be able to select and update the records/rows on locked tables.
On cases when we are suffering from locks or suspecting from processes that may lock others we can use the Sysprocesses system table to get information on processes that are running on the SQL Server.
Two columns of the SysProcesses table are:
spid : id of the sysprocess,
blocked : id of the sysprocess that is blocking the sysprocess with spid.
If you open a query editor your editor screen will display the spid of your connection to the sql server.
In SQL Query Analyzer you can see the spid of the processes that are run from that editor screen on the status bar of the screen.
For example, your SQL Server Query Analyzer query editor screen has spid 57.
Run the below statements on a table,
BEGIN TRAN
UPDATE SampleTable SET SampleColumn = DummyValue
"Begin Tran" command will create a transaction that will keep the lock existing until a Commit or a RollBack statement is called. So we have a locking process now. We can see this locking process if we run a select query on SysLocks. But we can not detect the process 57 by running the select query on SysProcesses.
But if we open an additional query editor window we will have a new spid. Let's assume that the new spid is 58.
And if you run a query against the locked table this current process will be waiting for the end of the prior query.
Untill the Commit or Rollback command is run for the open transaction on spid 57, the process 58 will not be able to complete its execution.
SELECT * FROM SampleTable -- This select statement which is running on spid 58 is waiting because of the lock on the SampleTable as a result of spid 57
Now if you run the below query on SysProcesses on a third query editor window
SELECT * FROM SysProcesses
The result will be similar as below which states that 57 is blocking processid 58
| spid | blocked |
| ------ | ------ |
| 58 | 57 |
After you run "Rollback" on the editor window with processid 57, the lock on the table will be released.
The query below may help you to determine which processes are locking other processes running on the SQL Server.
SELECT * FROM SysProcesses WHERE spid IN (
SELECT blocked FROM SysProcesses
) AND blocked = 0
By this query you can find the queies that are the head of chain of blocking processes.