Find blocking by T-SQL Queries

What is blocking

Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency. As mentioned previously, in SQL Server, blocking occurs when one session holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is small. When the owning session releases the lock, the second connection is then free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance. Source: https://docs.microsoft.com

DMV ‘sys.dm_exec_requests’  provides details on all of the processes running in SQL Server. Its returns information about each request that is executing in SQL Server. Use any one script out of three.

Here is a another script where the result will be in below table.

Kill blocking process

1) Use an activity monitor to kill the process

Right-click on the SQL instance name -> Activity monitor -> Expand the process section -> Find the relevant process id and right-click to kill the process.

“Please let us know if there are any changes/modifications required. Feel free to comment below – We always appreciate hearing your tips.”

Facebook Comments Box
Please like or share this post if you found it helpful, thank you.
Share on Facebook
Facebook
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Email this to someone
email
Print this page
Print

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *