SQL Server: 8 different ways to Detect a DeadLock in a Database

In the previous post, I shared T-SQL script to generate a DeadLock situation in SQL Server Database.
In this post, I am sharing different scripts to detect a Deadlock in SQL Server.

1. Using SP_LOCK, you can find the WAIT status for blocking sessions:

SQL Server SP_Lock

2. Using sys.sysprocesses:

3. Using common DMV:

4. Using sys.dm_tran_locks:

5. Enable required trace flags to log DeadLock related information in Tracefile:

6. Count total number of DeadLock:

7. Using Extended Events and below script to create Extended Event to monitor DeadLock:

SQL Server Extended Event For Deadlock

You can also use SSMS to create Extended Events for DeadLock, and you can monitor the live status of the server.

SQL Server Extended Event For Deadlock In SSMS

8. Using SQL Server Profiler:
SQL Server Profiler has three different events to capture a deadlock.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

1 Comment on "SQL Server: 8 different ways to Detect a DeadLock in a Database"

Notify of
Sort by:   newest | oldest | most voted

thank you sir, one of the best article and blog……