SQL Server view process blocking and processing

Back up before modifying or deleting data, back up first, back up (important things say three times)!

1, first, look at the thread, analyze whether there is a blocking process, blocked>0 are currently blocked processes
SELECT * from sysprocesses where blocked >0 order by blocked;

2. After locating the blocked thread, you want to continue to see who is blocking the process and analyze the source that caused the blocking

SELECT * from sysprocesses WHERE spid =spid_no (This is the process ID you want to parse)

3. See which SQL this process is executing, find the cause of the problem
DBCC INPUTBUFFER (SPID);

4. If you want to end the current process, kill the SQL that currently causes blocking
Kill spid

The above is the manual analysis, the current production of what is blocking and automatic analysis of the source, I will provide a self-organized automated analysis process, pro-test effective, can be directly used:

Use master
Go
declare @spid int, @bl int
DECLARE S_cur CURSOR for
Select 0, blocked
From (SELECT * from sysprocesses where blocked>0) a
Where NOT EXISTS (SELECT * from sysprocesses where blocked>0) b
where A.blocked=spid)
Union select spid,blocked from sysprocesses where blocked>0
OPEN S_cur
FETCH NEXT from S_cur to @spid, @bl
While @ @FETCH_STATUS = 0
Begin
If @spid =0
Select ' Causes the database deadlock to be:
' + CAST (@bl as VARCHAR (10)) + ' process number, which executes the SQL syntax as follows '
Else
Select ' Process number spid: ' + CAST (@spid as VARCHAR (10)) + ' by ' + '
Process number spid: ' + CAST (@bl as VARCHAR (10)) + ' block, whose current process executes the SQL syntax as follows '
DBCC InputBuffer (@bl)
FETCH NEXT from S_cur to @spid, @bl
End
CLOSE S_cur
Deallocate s_cur

SQL Server view process blocking and processing