How to kill oldest open transaction automatically?

Rabia Kurnaz 386 Reputation points
2023-04-11T06:42:51.3066667+00:00

Hello, I want to create a job that automatically kills the query if you work for more than 60 minutes. Could you help me please?

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,826 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2023-04-11T21:31:59.66+00:00

    You could schedule something like this to run periodically:

    DECLARE @sql nvarchar(MAX)
    SELECT @sql = string_agg(concat('KILL ', r.session_id), char(13) + char(10))
    FROM   sys.dm_exec_sessions s
    JOIN   sys.dm_exec_requests r ON s.session_id = r.session_id
    WHERE  s.is_user_process = 1
      AND  datediff(MINUTE, r.start_time, sysdatetime()) > 60
      AND  r.command NOT IN ('BACKUP', 'RESTORE')
    EXEC(@sql)
    
    

    However, I am quite sure that you will regret it sooner or later. For instance, when your reindexing job gets killed. I've made an attempt to exempt BACKUP and RESTORE from the menace. Beware that I have not actually tested this, so I urge you do this before you slap on to a server of any importance. The query requires SQL 2017 or later.

    0 comments No comments

0 additional answers

Sort by: Most helpful