Database Research & Development

  • Home
  • NoSQL
    • NoSQL
    • Cassandra
  • Databases
    • Database Theory
    • Database Designing
    • SQL Server Coding Standards
    • SQL Server
    • PostgreSQL
    • MySQL
    • Greenplum
    • Linux
  • Interviews
    • SQL Server Interviews
    • MySQL Interviews
    • SQL Puzzles
  • DBA Scripts
    • SQL Server DBA Scripts
    • PostgreSQL DBA Scripts
    • MySQL DBA Scripts
    • Greenplum DBA Scripts
  • Home
  • Blog Archives !
  • (: Laugh@dbrnd :)
  • Contact Me !
sqlserverinterviews
Home 2017 January SQL Server: DBCC INPUTBUFFER to find the last statement executed by a SPID

SQL Server: DBCC INPUTBUFFER to find the last statement executed by a SPID

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');
Open a transaction and UPDATE one record:
1
2
3
4
BEGIN TRANSACTION
UPDATE tbl_Students SET StudName = 'Meera'
WHERE StudID = 1
GO
Open a new session, UPDATE a same record:
1
2
3
UPDATE tbl_Students SET StudName = 'Anvesh'
WHERE StudID = 1
GO
Execute SP_WHO2 and find SPID of lead blocker transaction:
In my enviorment, I have found spid is: 151. You can find block by spid in BlkBy column
1
SP_WHO2
Execute DBCC INPUTBUFFER to find out last statement executed by SPID:
1
DBCC INPUTBUFFER (151)
Find out lead blocker query:
1
2
3
4
5
EventType Parameters EventInfo
-------------- ---------- --------------------
Language Event 0 BEGIN TRANSACTION
UPDATE tbl_Students SET StudName = 'Meera'
WHERE StudID = 1
Jan 13, 2017Anvesh Patel
SQL Server: Avoid the use of Indexes on VARCHAR columnsSQL Server 2016: Script to find last statement executed by a SPID using sys.dm_exec_input_buffer
Anvesh Patel
Anvesh Patel

Database Engineer

January 13, 2017 SQL ServerAnvesh Patel, block transaction, database, database research and development, DBCC INPUTBUFFER, dbrnd, DeadLock, SPID, SQL Query, SQL Server, SQL Server Administrator, SQL Server Monitoring, SQL Server Performance Tunning, SQL Server Tips and Tricks, TSQL
About Me!

I'm Anvesh Patel, a Database Engineer certified by Oracle and IBM. I'm working as a Database Architect, Database Optimizer, Database Administrator, Database Developer. Providing the best articles and solutions for different problems in the best manner through my blogs is my passion. I have more than six years of experience with various RDBMS products like MSSQL Server, PostgreSQL, MySQL, Greenplum and currently learning and doing research on BIGData and NoSQL technology. -- Hyderabad, India.

About DBRND !

dbrnd

This is a personal blog (www.dbrnd.com).

Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated.

Feel free to challenge me, disagree with me, or tell me I’m completely nuts in the comments section of each blog entry, but I reserve the right to delete any comment for any reason whatsoever (abusive, profane, rude, or anonymous comments) - so keep it polite.

The content of this website is protected by copyright. No portion of this website may be copied or replicated in any form without the written consent of the website owner.

Recent Comments !
  • Anvesh Patel { Sure will do... } – May 27, 12:43 PM
  • Anvesh Patel { Great... } – May 27, 12:41 PM
  • Anvesh Patel { Great... } – May 27, 12:39 PM
  • Anvesh Patel { Great... } – May 27, 12:36 PM
  • Anvesh Patel { Great... } – May 27, 12:28 PM
  • Anvesh Patel { Great... } – May 27, 12:27 PM
  • Anvesh Patel { Great... } – May 27, 12:16 PM
  • Older »
Follow Me !
  • facebook
  • linkedin
  • twitter
  • youtube
  • google
  • flickr
© 2015 – 2019 All rights reserved. Database Research & Development (dbrnd.com)
Posting....