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.

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.

Script: open transactions with text and plans

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
http://csharp-video-tutorials.blogspot.com/2015/09/how-to-find-blocking-queries-in-sql_3.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/how-to-find-blocking-queries-in-sql_3.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists