none
TSQL script for Deadlocks RRS feed

  • Question

  • Hello,

    Is there a way to capture via a TSQL script, the deadlocks which last 10 minutes or longer for a particular database? 

    Please advise.

    Thanks,

    Veena

    Thursday, January 23, 2020 4:15 PM

Answers

All replies

  • Hi Veena,

    I always use this solution for deadlocks -> https://www.brentozar.com/archive/2017/12/introducing-sp_blitzlock-troubleshooting-sql-server-deadlocks/ you can filter by database or login or whatever you need.

    • Marked as answer by Veena Srinivas Thursday, January 23, 2020 5:17 PM
    Thursday, January 23, 2020 5:06 PM
  • Thank you Gonzalo,  does this script allow to filter by length of deadlock too?  I only want to see deadlocks that are there for 10 minutes or more.
    Thursday, January 23, 2020 5:18 PM
  • yeap, you can use 
    • @StartDate: The date you want to start searching on.
    • @EndDate: The date you want to stop searching on.

    parameters to play with the date

    Thursday, January 23, 2020 5:42 PM
  • Is there a way to capture via a TSQL script, the deadlocks which last 10 minutes or longer for a particular database? 

    Deadlocks never last for 10 minutes. Deadlocks are usually resolved within five seconds, as SQL Server regularly checks for deadlocks and resolves them by injectiong an error in one of the processes. To wit, a deadlock is when two or more processes block each other, so that none of them can proceed.

    I guess what you have in mind are plain blocking situations where a long-running processes blocks other processes. But they are not deadlocks.
    You can however, find these by query this way:

    SELECT * FROM   sys.dm_os_waiting_tasks WHERE  wait_duration_ms > 3600 * 10 * 1000
      AND  blocking_session_id IS NOT NULL


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 23, 2020 10:27 PM