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.

Anvesh Patel

Leave a Reply

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

Notify of
avatar
Sort by:   newest | oldest | most voted
Priya
Guest

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

wpDiscuz