deadlock
what is a deadlock in sqlserver how it can be solved .
A deadlock occurs when two or more SQL Server processes have locks on separate database objects and each process is trying to acquire a lock on an object that the other processes have previously locked.
When SQL Server identify deadlock, it ends the deadlock by choosing one process as avictim and kill this processe and by that action he lets the other one to continue to run.
The victim which been chosen by SQL Server is usually the process that has used the least amount of server resources up to this point.
In this link, you can learn how to trace deadlocks and how to reduce them.
Blocking processes
In order to capture processes block, run this procedure:
EXEC sp_who2
Then we look at the BlkBy field. If there is a number in a cell, it's means, there is a process that block one or more process
Now, the next step will be to identify what this block process do. For this purpose we can run this code:
DECLARE @pID int -- get the block process id
EXEC sp_who2 @pID
DBCC INPUTBUFFER (@pID)
GO
Or this one:
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle
FROM master.dbo.sysprocesses
WHERE spid = @pID
SELECT * ,db_name(dbid)
FROM ::fn_get_sql(@Handle)
Finally, after investigating the block process, and decide to KILL this process USE this command:
KILL @pID