Specific operations:
Find the owner of the current blocking statement based on the spid and blocked in master. DBO. sysprocesses, and then use DBCC inputbuffer () to view the blocking statement.
Example:
Open three query analyzers A, B, and C
Create a test table testdia
Create Table testdia (id int );
Execute the following statement in:
Begin tran
Insert into testdia values (1 );
Execute the following statement in B:
Select * From testdia
Current situation: no result is displayed in B and the status is waiting.
Run the following statement in C:
Declare @ spid int, @ blocked int
Select top 1 @ blocked = blocked
From master. DBO. sysprocesses
Where blocked> 0
DBCC inputbuffer (@ blocked)
Technical Reference:
DBCC inputbuffer (TRANSACT-SQL)
displays the last statement sent from the client to the Microsoft SQL Server 2005 instance.
DBCC inputbuffer (session_id [, request_id])
[with no_infomsgs]
session_id
session ID associated with each active master connection.
request_id
requests to be precisely searched in the current session (batch ).
The following query returns request_id:
copy Code
select request_id
from sys. dm_exec_requests
where session_id =@spidwith
enables the option to be specified.
no_infomsgs
cancels all information messages with severity levels ranging from 0 to 10.