SQL Server tutorial for beginners - 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. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 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. http://www.sqlskills.com/blogs/paul/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