How to kill oldest open transaction automatically?

Question

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?

Answer 1

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.