What can cause InvalidOperationException: max pool size reached except for the obvious

I have an issue w开发者_C百科ith a windows service that accesses a SqlServer 2005 database. Every Monday it crashes with an InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I've checked all the obvious things like closing the db connections and readers and found no problems.

The strangest thing is that in the last case I set up perf monitor to record the number of connections in the pool and things like that and found that the Sql Server actually went down a few days before the service crashed, so all the SQL counters showed 0 (the service handles SQL Server stopping and tries to connect every few seconds until it reconnects successfully).

Before the SQL server was stopped, the amount of open connections was 1-2 all the time, so it really doesn't seem to be a problem with the connection count.

I'll be investigating the SQL Server logs for clues, but maybe you can give me an idea about what might be causing this exception if the actual number of connection is not a problem.

Update:

Checked the logs and it seems that the Sql Server wasn't down all the time. On Thursday it was stopped, but was restarted on Friday. The restart caused the SQL counters in perf mon to stop reporting data, so I have no real data about the number of connections at the time of the crash.

What I think Happenned is that, Ado queued the connection request (because probably the SQL REader service was not stopped with the native provider) and when the SQL Server booted up again it sent all this request to him at this time. This is what caused the Invalid operation exception. Sqlserver Provider does such things (all in all it's his responsibility to manage connections not SQL Servers)

What You should do Dro is check in the code whether the Sql Service is up an running. U can easily use WMI for that or SQL SMO. Something like 3 lines of code and You're done.

Luke

Eventually Google helped me find a solution for this one. As per this blog this exception can occur when 2 or more threads try to access a not responding SQL server instance at the same time. It is enough to wrap the conn.Open() call in a lock and the error is gone. Bizarre but works.

Is there some maintenance sheduled for the night before Monday? This may be responsible for this kind of errors.