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.
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]')
if exists (select * from sys.tables where name = '#OLD_SPIDS')/* 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 */
EXEC ('DROP TABLE #OLD_SPIDS')
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)
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
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)
insert into [LOGSCHEMA].[Kill_Long_Running_tran_Log]
values (current_timestamp, @kill_session_statement)
DROP TABLE #OLD_SPIDS