c# - .NET Connection pool doesn't recover after short forced single user mode of target DB -
our app connecting our db using following connection string:
data source=<ip>;initial catalog=<dbname>;user id=<account>;password=<password>
it uses try store data in loop can simplyfied following pseudocode:
bool stored; { (using)(sqlconnection conn = new sqlconnection(staticconnectionstring)) { try { conn.open(); //create sp command , execute here stored = true; } catch(exception e){/* log , sleep few secs */} } }while(!stored)
we experienced issues blocking , extremely high disk i/o due wrongly designed index. put db single user mode:
alter database <dbname> set single_user rollback immediate go
and fixed issue , flipped db multi_user. after other instances of our exe reconnected fine db - exe trying insert quite lot of data (previous loop running in dozens parallel threads different records) hasn't recovered hour after (than restarted). continuously getting following exception during attempt connect:
system.data.sqlclient.sqlexception (0x80131904): cannot open database "<dbname>" requested login. login failed. login failed user '<account>'. @ system.data.providerbase.dbconnectionpool.trygetconnection(dbconnection owningobject, uint32 waitformultipleobjectstimeout, boolean allowcreate, boolean onlyonecheckconnection, dbconnectionoptions useroptions, dbconnectioninternal& connection) @ system.data.providerbase.dbconnectionpool.trygetconnection(dbconnection owningobject, taskcompletionsource`1 retry, dbconnectionoptions useroptions, dbconnectioninternal& connection) @ system.data.providerbase.dbconnectionfactory.trygetconnection(dbconnection owningconnection, taskcompletionsource`1 retry, dbconnectionoptions useroptions, dbconnectioninternal& connection) @ system.data.providerbase.dbconnectionclosed.tryopenconnection(dbconnection outerconnection, dbconnectionfactory connectionfactory, taskcompletionsource`1 retry, dbconnectionoptions useroptions) @ system.data.sqlclient.sqlconnection.tryopen(taskcompletionsource`1 retry) @ system.data.sqlclient.sqlconnection.open() @ <our call connection.open> clientconnectionid:9dcf1a45-0f53-4a0f-be8b-63d871ca0afd
where threads getting problem same internal connection (.net framweork connection pool clientconnectionid guid repeats in stack traces issue). in tcpview nd process monitor see app not trying create new connection server - somehow internally remembers connection failed due single_user mode , rethrows exception on next attempt, , pool doesn't try clear such connection. attempted force several full gc - no change, detach attach database - no change; forbid network acess sql server , reenable - no change.
why isn't connection pool throwing away failed connections? how can remedy this?
Comments
Post a Comment