(0x80040E31) Timeout expired

[Waldy]

Hi there,
I have some code running in .ASP pages that 99 times out of a
hundred will run OK, but every now and again, a timeout error occurs. The
error is :

Microsoft OLE DB Provider for SQL Server (0x80040E31)
Timeout expired

The lines of code where is happens are executing insert or update statements
which surprised me. I could understand it more if it was a select
statement. Why is this happening. Is it refering to the Connection
Timeout, Query Timeout or some other setting? Or is it not possible to tell
from the error number? How do you set the timeout values?

Thanks.

[Erland Sommarskog]

"Timeout expired" is usually the command timeout, which you can set on
the Connection object or the Command object. If you set the timeout to 0,
this means "wait forever".

A possible reason you get this for INSERT or UPDATE could be blocking.
Another process runs some operation which holds a table lock - that
would typically be a long-running operation.

If your application connects and disconnects, it could be a connection
timeout due to some network glitch. But in that case, you would see
this more randomly all over the application.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Waldy]

Hi Erland,
thanks for that.

> If your application connects and disconnects, it could be a connection
> timeout due to some network glitch. But in that case, you would see
> this more randomly all over the application.

That was my original thought, but IIS is using SQL Server installed on the
same machine.

> A possible reason you get this for INSERT or UPDATE could be blocking.
> Another process runs some operation which holds a table lock - that
> would typically be a long-running operation.

I will try putting NO_LOCK on the Select statements to see if that has any
effect.

[Erland Sommarskog]

Waldy (som...@microsoft.com) writes:
> I will try putting NO_LOCK on the Select statements to see if that has any
> effect.

The hint is NOLOCK.

Beware that NOLOCK can give you inconsistent and unreliable results. If
you are on SQL 2005, you may be interesting in using snapshot isolation
or read committed snapshot.

[Waldy]

I just noticed that in OLE DB header file, 80040E31 is defined as
DB_E_ABORTLIMITREACHED with the message text of "Execution aborted because a
resource limit has been reached; no results have been returned". So why am
I getting "Timeout expired"? A resource limit would make much more sense
than a timeout when it is being caused by an insert statement.

[Erland Sommarskog]

A reousrce limit implies that you have run out of memory, locks or
similar. A timeout does not really have anything to do with resources.
You have simply said "I am prepared to wait so many seconds fot this
query to start producing results". It's user-defined limit.