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
The session ID associated with the active connection.
Request_id
The request (batch) to be precisely searched in the current session ).
The following query returns request_id:
Copy code
SELECT request_id
FROM sys. dm_exec_requests
WHERE session_id = @ spidWITH
Enable the option to be specified.
NO_INFOMSGS
Cancels all information messages with severity levels ranging from 0 to 10.