Post by fniles
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on Windows Server 2003 R2 Enterprise x64 Edition SP
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
First of all, isolate exactly there the problem appears. Does it happen
when you connect, or does it happen when you run the command? You seem
to be thinking that it happens when you connect, but there all reason
to double-check. The course of action depends on where it happens.
If it it happens on connection, the error may occur in the connection
pool, that is, there is not any available connection in the pool. I
will have to admit that I don't know for sure whether a timeout in the
connection pool gives exactly this error.
Yet a possible reason is simply a network glitch. DNS somewhere dies
for a moment or something similar. I have experienced short outages in the
network where I work have not been able to reach some servers for a
few minutes. However such errors usually gives a different error message,
as the server cannot be reached at all. I think this message indicates
that connection was initialised, but never completed.
In this case, there may be problems with available worker threads in
SQL Server. I recall a thread in our internal MVP forum where quite
some few experienced people have fought this error with limited success.
I think there is a hotfix, but it may not address everything.
SP4 for SQL 2005 came out recently, I think you install it as a start.
Post by fniles
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?
As long as you can accept the delay, that's a way. You can also use the
.ConnectionTimeout property to permit for more time.
Finally, do you have any LOGON triggers in place?
Erland Sommarskog, SQL Server MVP, ***@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