How to find blocking queries in sql server

sql server find blocking processes
sql server list blocking processes
dbcc opentran example
sql server kill spid
sql server kill connections
sql server kill query
sql server kill transaction

In this video we will discuss, how to find blocking queries in sql server.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Blocking occurs if there are open transactions. Let us understand this with an example.

Execute the following 2 sql statements
Begin Tran
Update TableA set Name='Mark Transaction 1' where Id = 1

Now from a different window, execute any of the following commands. Notice that all the queries are blocked.
Select Count(*) from TableA
Delete from TableA where Id = 1
Truncate table TableA
Drop table TableA

This is because there is an open transaction. Once the open transaction completes, you will be able to execute the above queries.

So the obvious next question is - How to identify all the active transactions.

One way to do this is by using DBCC OpenTran. DBCC OpenTran will display only the oldest active transaction. It is not going to show you all the open transactions.
DBCC OpenTran

The following link has the SQL script that you can use to identify all the active transactions.

The beauty about this script is that it has a lot more useful information about the open transactions
Session Id
Login Name
Database Name
Transaction Begin Time
The actual query that is executed

You can now use this information and ask the respective developer to either commit or rollback the transactions that they have left open unintentionally.

For some reason if the person who initiated the transaction is not available, you also have the option to KILL the associated process. However, this may have unintended consequences, so use it with extremen caution.

There are 2 ways to kill the process are described below

Killing the process using SQL Server Activity Monitor :
1. Right Click on the Server Name in Object explorer and select "Activity Monitor"
2. In the "Activity Monitor" window expand Processes section
3. Right click on the associated "Session ID" and select "Kill Process" from the context menu

Killing the process using SQL command :
KILL Process_ID

What happens when you kill a session
All the work that the transaction has done will be rolled back. The database must be put back in the state it was in, before the transaction started.

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

[raqibul]

You did a great work. We can see n number of videos and articles of many technology in internet .but many of them are scattered . You did it in a systematic way so that people can find it easy and read and UNDERSTAND EASY. Well Done !.
Thanks for nice tutorial.

[rogersantos]

You save a lot of peoples life with this video hehe, very usefull thanks a lot! you are the best!!!!

[AHMEDALDAFAAE]

That guy is amazing! Every time I have a question, I will jump to those videos

[kingscrusher]

Thank you so much - helped greatly in resolving a deadlock - happy holidays and new year when it comes :)

[burropoco]

Very helpful and well explained. Thank you.

[uzwalkirankumarreddy]

Nice video Venkat garu.  Simply superb!!!

[divvikumar]

thank you🙏 for every thing it another great session

[hyd-knsi]

Jolly good tutorial. Thank you so much venkat.

[anshuldubey]

Sir, You are doing a great job. Amazing Videos, A big thanks!!

[hemantkilanje]

Today interviewer asked this question to me... How identify queries blocking... How to find blocking queries... I didnt get answer... And now I chek your video... Bt how to create that join query.. Plz explain that query

[ArunMaskeri]

Venkat sir, i think we can use nowait hint also on the query which will tell wether query is blocked or not, could please comment pls

[pavankondru]

Could you please provide the, Finding of open transactions Query !

[anshuldubey]

One question: I faced this issue, If a job is running on a particular Database every 10 secs and we are trying to create the replica of that Database, does that job blocks creation of replica in some way? I was unable to create a complete DB replica as the Stored procedures involved in the job weren't replicated.

[subhrapratimbiswas]

The link is not opening, please share the script that you can use to identify all the active transactions.

[alirana]

Hi Sir,
i am unable to find qury which shows all open Transactions with login id because website is down . can anyone help me.

[rbramesh]

Sir IAM one question pls under stand.
Iam run this query
Begin tran
Update table employee
Set empname='mani'
Where empid=2
Execute
Block was created.block session (54)that time IAM deleate this query Begin tran
Update table employee
Set empname='mani'
Where empid=2
Next
I write a select query.
That's time how to see THIS QUERY
Begin tran
Update table employee
Set empname='mani'
Where empid=2

PLS SOLVE THIS PROBLEM(sorry for any mestics)