SQL Server - execution timeout

Asked By simon on 23-Feb-11 06:29 AM
Hi,

I would like to test something so I have setted the execution-timeout
in my SSMS to 1 second.

Then I created the following SP:

CREATE PROCEDURE dbo.testTimeOut
AS

WAITFOR DELAY '00:00:10'

If I execute procedure, it waits until the end, so no timeout error is
thrown in my managment studio.
I tried also with linked server and execute the same procedure there,
but still the same, always waits until the end.

Any idea?

Thanks,
Simon




Erland Sommarskog replied to simon on 23-Feb-11 05:52 PM
simon (zupan.net@gmail.com) writes:

I have a vague memory of that I have been into this discussion before,
but I do not remember the outcome. And, no, I was not table to provoke a
timeout.

I have sent a question to our internal MVP forum to see if anyone has a
clue.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
Erland Sommarskog replied to Erland Sommarskog on 24-Feb-11 05:42 PM
Erland Sommarskog (esquel@sommarskog.se) writes:

OK, so this really weird. Tonight when I tried, the timeout worked as
advertised. And to make it even more crazy: Last night I closed and
restarted SSMS with no effect. But I had SSMS running all day, and suddenly
the change has effect.

And then I reset the timeout to 0, but the timeout kept coming. Yes,
even after a restart.

I have no idea what is going on.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
simon replied to Erland Sommarskog on 28-Feb-11 03:41 AM
ly
avedeno besedilo -

I found that discussion. The outcome was that this is a bug :)
So, If I want to test something I will have to create a connection
from some other program.
For ADO.NET command.timeout works.

What I wanted to test is what happens with transaction if timeout on
the client is the case.
Well the transaction remains active for some time(30 seconds)  unless
XACT ABORT is set to on.
If XACT ABORT is ON than rollback is executed immediatelly when
timeout is happened.

But I have some other problem. I have BizTalk connected to my sql
server and if I have distributed transaction enabled on Biz Talk than
on my SQL server I get many uncommited transactions.
The number grows over time:
SELECT * FROM sys.dm_tran_active_transactions

Obviously, Biz Talk does not commit some transactions.
It calls in transaction one procedure every 10 seconds and about 10
transactions each day remains open. I do not know why.
Is there isome setting where I can say if transaction is active more
than an hour, than rollback that transaction?

Thank you,
Simon
Erland Sommarskog replied to simon on 28-Feb-11 09:46 AM
simon (zupan.net@gmail.com) writes:

I did not find it. Do you have a link?


Correct. Therefore the client should alwyas issue a "IF @@trancount > 0
ROLLBACK TRANSACTION" after a timeout.


You would have to write a job that monitors transaction and kills
connections as needed. But it could have unpleasant consequences. And
you may find that dormant connections with distributed transactions are
not easily killed at all.

As for the problem with BizTalk, I do not have any experience of BizTalk,
so I cannot comment.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx