ODBC timeout error on simple query.

Question

We are getting the following ODBC timeout errors when executing sql code against a SQL Server database.

"Connectivity error: [Microsoft][ODBC SQL Server Driver]Timeout expired" "S1T00"

The confusing thing here is that our stack dump says that the SQL code it is executing when the timeout occurrs - is very simple:

SELECT @@TRANCOUNT AS TranCount

or sometimes...

BEGIN TRANSACTION

Our client says that the load on the database server at the time that these errors occur, is not overwhelming. What could possibly cause those simple statements to timeout? I believe the timeout setting is fairly short (between 1 and 5 seconds)... but still, SELECT @@TRANCOUNT should be instantaneous.

Any ideas?

Answer 1

It may be driver-dependent, but I think the ODBC timeout may be handled by the client. This means that any network delays or even slight server hiccups caused by anything from a virus scanner to miscellaneous processes could easily cause a 1 or 2 second delay. So if your timeout is actually 1 second, it doesn't seem surprising that you might get timeout errors.

It seems that even 5 seconds is a bit on the low side for a reasonable timeout. This is assuming that the client and server are not on the same physical machine.