0

Is there any parameter to prevent long transactions? I would like to automatically rollback all transaction that takes more than 5 minutes. Is it possibile? I'm working on a Microsoft SQL server 2008 R2.

Background notice:

I'm asking this because I'm working with many awful application: In case of application exception they show a modal alert, transaction is released ONLY when the alert is closed. I really know this seems a joke, but it isn't! I can not change this apps and I can not fire their developers but I need to solve this problem: long transaction (can be also a simple transaction with low cost) must take less then 5 minute. Otherwise some tables will be locked until modal close.

3 Answers 3

1

There is an option called 'query governor cost limit' which takes an integer value. The value you specify is what the max time in seconds that a query can run. Please note that this is based off of the estimated time and value. If a cost exceeds this, the query will NOT be allowed to execute. This is not an exact science, some may run longer and some that are disallowed may run shorter as it's based on an estimated cost.

http://technet.microsoft.com/en-us/library/ms190419(v=sql.105).aspx

1
  • I don't know if I well understand this option, however my problem is a looooong transaction, not an expensive transaction... I will update my question to better explain my situation.
    – Tobia
    Sep 24, 2014 at 8:34
0

You might be interested in this other StackOverflow thread:

Limit resources available to query in SQL Server

https://stackoverflow.com/questions/9336194/limit-resources-available-to-query-in-sql-server

0

This is my temporary answer:

DECLARE @ProcessId int=null;

SELECT top 1 @ProcessId=p.spid
--, s.host_name, s.program_name, s.original_login_name, q.text as sql, wp.spid as waiting_sess, ws.host_name as waiting_host_name, ws.program_name as waiting_program_name, wr.wait_time as waiting_time, wq.text as waiting_sql
FROM sys.sysprocesses p
join sys.dm_exec_sessions s on s.session_id=p.spid
join sys.dm_exec_connections c on c.session_id=p.spid
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS q
join sys.sysprocesses wp on wp.blocked=p.spid
join sys.dm_exec_sessions ws on ws.session_id=wp.spid
join sys.dm_exec_requests wr on wr.session_id=wp.spid
CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wq
WHERE p.spid in (select distinct blocked FROM sys.sysprocesses where blocked>0) 
and p.blocked<=0
and wr.wait_time>300000 --5min
-- uncomment the next line to really kill process
-- if @ProcessId is not null EXEC('KILL ' + @ProcessId);

This is a query to get all blocking process with a waiting resource >5 minutes (300000 millis). After this it execs a kill with that pid. I will schedule this stored procedure with Sql server agent.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .