Transaction and Connection Timeout

17 Mar 2006 6:53 PM

Michael X

We are running a lot of unit tests for the product which is built on .NET 1.1
with ADO.NET, and SQL server 2000. Recently some unit test encount this
error:
"System.Data.SqlClient.SqlException : Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding."

Here are some settings we have
connection pool = True
CommandTimeout = 300 (for command object, not the one in Connection object)

The one or several sql commands are wrapped in a transaction. It happened
randomly in different unit test. One of long process stored procedure hit
this problem quite often, but some very short db execution command happened
as well. But all failure happened way before 300s. Through research I notice
there is a transaction timeout which inherits value from connection timeout.
By default it is 15 second. So my questions are:
1)    How does transaction timeout relate to CommandTimeout?
2)    Which other areas I should look into to prevent Timeout? Any SQL server
setting can help reduce the chance of Timeout?
3)    Does it related to connection pool?
4)    Does it related to deadlock?

Thanks in advance,
Michael

17 Mar 2006 9:17 PM

Marina Levit [MVP]

The timeout could be due to a database deadlock due to transactions. If
there is a long running transaction holding on to a resource, and another
connection is trying to get at the same data locked by the transaction, then
this could happen.

Show quote

"Michael X" <Michael X@discussions.microsoft.com> wrote in message
news:8A014733-455D-4FD0-B2D8-F2B38A012504@microsoft.com...
> We are running a lot of unit tests for the product which is built on .NET
> 1.1
> with ADO.NET, and SQL server 2000. Recently some unit test encount this
> error:
> "System.Data.SqlClient.SqlException : Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding."
>
> Here are some settings we have
> connection pool = True
> CommandTimeout = 300 (for command object, not the one in Connection
> object)
>
> The one or several sql commands are wrapped in a transaction. It happened
> randomly in different unit test. One of long process stored procedure hit
> this problem quite often, but some very short db execution command
> happened
> as well. But all failure happened way before 300s. Through research I
> notice
> there is a transaction timeout which inherits value from connection
> timeout.
> By default it is 15 second. So my questions are:
> 1) How does transaction timeout relate to CommandTimeout?
> 2) Which other areas I should look into to prevent Timeout? Any SQL server
> setting can help reduce the chance of Timeout?
> 3) Does it related to connection pool?
> 4) Does it related to deadlock?
>
> Thanks in advance,
> Michael
>

17 Mar 2006 11:41 PM

Michael X

Thanks for the reply. Deadlock is one of the areas our db team is looking
into. Are there any other area I should pay attention to since it timeout on
some short transaction as well even before reach the long transcation ones.

And I'm still bit confuse with CommandTimeout, and transcation timeout.
Anywhere I can find information about how these two setting affect each other.

Michael