This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Are you SQL Server DBA?
Have you ever faced deadlock situations?
What are your steps to remove a deadlock?
Do you know, how to get the last statement executed by lead blocker transaction?
If you are a SQL DBA, you must know about this.
You can access few related articles:
SQL Server: Database Lock, Deadlock, Block and Isolation Interview Questions and Answers (Day-1)
SQL Server: 8 different ways to Detect a DeadLock in a Database
DBCC INPUTBUFFER:
Using this DBCC command, you can get the last statement executed by a particular SPID which is playing a lead blocker role.
Please do not blindly kill the connections by SPID. You must know the root cause of deadlock and its previous lead blocker transaction.
Prepared a one example demonstration on this.
Create a table with sample data:
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE tbl_Students ( StudID INT ,StudName VARCHAR(20) ) GO INSERT INTO tbl_Students VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Jeeny'),(4,'Roy'); |
1 2 3 4 |
BEGIN TRANSACTION UPDATE tbl_Students SET StudName = 'Meera' WHERE StudID = 1 GO |
1 2 3 |
UPDATE tbl_Students SET StudName = 'Anvesh' WHERE StudID = 1 GO |
In my enviorment, I have found spid is: 151. You can find block by spid in BlkBy column
1 |
SP_WHO2 |
1 |
DBCC INPUTBUFFER (151) |
1 2 3 4 5 |
EventType Parameters EventInfo -------------- ---------- -------------------- Language Event 0 BEGIN TRANSACTION UPDATE tbl_Students SET StudName = 'Meera' WHERE StudID = 1 |
Leave a Reply