SQL Server: T-SQL JOB to kill sessions with long-running transactions

We have a situation in a build/test server in which the application server (jboss, in this case) is unceremoniously killed by the build process.

This is leaving us with transactions that are not committed, nor are they rolled back. I like to call these zombies. SQL Server thinks they're just happily executing.

Because SQL Server has no server side lock, transaction, or query timeout, I developed this job to simulate a 10 minute transaction timeout.

It's not sophisticated, but it will give you a good starting point in case you should have to create your own .

if not exists (select * from sys.schemas where name = 'LOGSCHEMA')
EXEC ('CREATE SCHEMA [LOGSCHEMA] AUTHORIZATION [dbo]')
GO

if exists (select * from sys.tables where name = '#OLD_SPIDS')
EXEC ('DROP TABLE #OLD_SPIDS')
GO

 /* NOTE: In the following, I fully qualified the Kill_Long_Running_tran_log table as dbname.schema.tablename. DB_NAME will need to be changed */

if not exists (select * from sys.tables where name = 'Kill_Long_Running_tran_Log')
EXEC ('CREATE TABLE [/*change db_name */DB_NAME].[LOGSCHEMA].[Kill_Long_Running_tran_Log] (msg_timestamp datetime2(7), msg varchar(256))')

DECLARE @session_to_kill int = 0
DECLARE @kill_session_statement nvarchar(max)

select dtst.session_id
, dtst.transaction_id
, dtat.name
, dtat.transaction_begin_time
INTO #OLD_SPIDS
from sys.dm_tran_session_transactions dtst
join sys.dm_tran_active_transactions dtat
on dtst.transaction_id = dtat.transaction_id
where dtat.transaction_begin_time < DATEADD(MINUTE,-10,current_timestamp)

select @session_to_kill = session_id
from #OLD_SPIDS
where transaction_begin_time = (select min(transaction_begin_time) from #OLD_SPIDS )

select @kill_session_statement = 'kill ' + cast(@session_to_kill as varchar(10))

if (@session_to_kill != 0)
begin
insert into [LOGSCHEMA].[Kill_Long_Running_tran_Log]
values (current_timestamp, @kill_session_statement)
exec (@kill_session_statement)
end 

DROP TABLE #OLD_SPIDS