ADO Connection after DB backups

[Rich DeWaal]

We stop a separate server with a SQL Server instance for backups at night
the restart after backups are completed. The ADO connection on our web
server (Win Svr 2k w/IIS) cannot reconnect to the database server unless the
web machine is rebooted. We have tried to restart IIS but it did not work.
Has anyone seen this problem before? Any thoughts on where to start?

R. DeWaal

 

[Sahil Malik [MVP]]

Rich,

This is a known issue with .NET 1.1. Essentially there are a few open
connections left in the connection pool when you reboot your database
server. But when you reboot the server, the web server (and it's connection
pool), has no idea that the connection pool is now corrupt. .NET 2.0 now has
a new static method called "ClearConnectionPool" (or something like that),
on SqlConnection which tells the web server to clear out the pool and start
afresh (clearing only marks the existing pool entries as corrupt, so it
cannot be used as an alternate to disposing or closing connections).

Now how do you do this in .NET 1.1.

a) I had written some code using reflection to do the same, which I can't
find right now LOL. If I do, I'll post it on my blog, but no guarantees of
it working. (Also I've gotta leave for the summit tommorow, so may not have
time tonight .. but I will post it eventually .. maybe in a week's time).
b) Don't stop your SQL Server, instead backup from a replicated copy (that
you can stop). <-- Better solution.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx