Blocked Process

Dealing with the blocked process

Blocking

When a process acquiring a lock on a object for its transactions to complete and at the same time the other process wants to acquire a lock on the same object

on which the first process is having a lock then the second process needs to wait

until the first one releases its lock. So the second process will be blocked by the first process is called as blocking

*** How to identify the blocked process ?

• select spid,cmd,status,blocked from master..sysprocess where blocked >0
• sp_who “spid” → gives information about spid ,uname,cmd,status and kill that process

Identifying SQL text of a process

• dbcc trace on (3604)
  • trace flags
    • 3604 → Displays output on the screen
    • 3605 → redirect the output to error log file

• dbcc sqltext(spid) {spid →id which is being blocked }