Sometimes, like when a piece of bad code goes into production or a query jockey is loose on the system, it is necessary to monitor user processes to prevent users from becoming 'blocked' by other users. You can determine blocked processes by examining the output of sp_who2 or by looking at the 'blocked' column in master..sysprocesses.
As a proactive procedure it is possible to set up a task that can be run as often as appropriate to watch for blocked users. In the example here, a single user name is monitored for blocks. This would only be appropriate if multiple users connect with the same user name as is the case in many web applications.
A task is set up to run every ten seconds. The GUI for the EM Scheduler does not provide options to set up tasks at any smaller time granularity than one minute. For this reason, the stored procedure sp_addtask must be used to schedule the task.
The task calls a stored procedure: check_for_blocks, that scans master..sysprocesses for blocked SPIDs. If no blocks are found the stored procedure closes. This is very fast and has little impact on the system. If a block SPID is found the stored procedure will wait for ten seconds to before rechecking for the blocked condition. If the block still exists after this ten second interval, a network message is broadcast to the support staff and another stored procedure -- find_blocker -- is called that will mail a detailed breakdown of the blocking process is mailed to the DBA for further analysis.