locked
how to monitor Longest Running Transaction using SQL Server Agent Alerts RRS feed

  • Question

  • hi there:

      I understand that this article has explained in details on how to monitor longest running transactions using agent alerts

    https://www.sqlservercentral.com/articles/monitoring-longest-running-transaction-using-sql-server-agent-alerts

    however, the author also put a note saying it only works for databases under read committed snapshot isolation level. 

    Since most of my databases are not snapshot isolation level, is there still a way to use sql agent to detect longest running

    transactions and send out alerts. 

     I fully understand how to complete this task using sql server schedule jobs but would prefer to use sql agent alerts as it puts less pressure on the job and gives me near real-time alerting . 

    Thank you

    Hui


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Tuesday, February 4, 2020 7:57 AM

Answers

  • Hello Hui,

    Unfortunately, for 'Longest Transaction Running Time',   it only shows activity when the database is under read committed snapshot isolation level. It does not log any activity if the database is in any other isolation level. This description please Refer to SQL Server, Transactions Object.

    So you have to set  database under read committed snapshot isolation if you want to using SQL Agent Alerts. Or you can use below methods as well:

    1. sys.dm_tran_database_transactions
    2. DBCC OPENTRAN

    Here is another article:Monitoring Long Running Transactions in SQL Server, you may find something in it.

    Hope it will help. Any further question, please let me know.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 5, 2020 7:09 AM

All replies

  • Hello Hui,

    Unfortunately, for 'Longest Transaction Running Time',   it only shows activity when the database is under read committed snapshot isolation level. It does not log any activity if the database is in any other isolation level. This description please Refer to SQL Server, Transactions Object.

    So you have to set  database under read committed snapshot isolation if you want to using SQL Agent Alerts. Or you can use below methods as well:

    1. sys.dm_tran_database_transactions
    2. DBCC OPENTRAN

    Here is another article:Monitoring Long Running Transactions in SQL Server, you may find something in it.

    Hope it will help. Any further question, please let me know.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 5, 2020 7:09 AM

  • Hello,
    Have you solved the question?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 11, 2020 10:35 AM