We have a WCF service hosted in IIS running on a server. The service connects to an instance of SQL Server running on another server. In our code, we open and close the connection for each request. The WCF service processes several queries per minute in the database.
If I restart the SQL service without stopping the WCF service, the WCF service starts logging errors in our error log, which is completely normal.
The problem is that sometimes (not always) after restarting the SQL service, the WCF service continues to report this error for each request:
Cannot open database "mydatabase" requested by the login. The login failed."
The error stack trace ends with:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
While this is happening, I can connect to the database using SQL Management Studio (from the same computer where the WCF service is running). To fix this problem, I need to recycle the application pool for my WCF service.
So my question is: what can lead to this and what can I do so that my application can recover after restarting SQL?
PS: In my dev block (also using IIS), when I do the same test, I get the same error several times (while the database is loading, I suppose), and then it starts working again.
source
share