[MSSQL2005] Lange ODBC connectie(s) worden random verbroken*

[o_f_course]

Ik onderhoud een applicatie die al een tijdje problemen geeft bij een klant tijdens een batch van >10 uur.
De applicatie heeft als mechanisme dat bij het opstarten ODBC connectie(s) worden geopend en deze worden opengehouden zolang de applicatie draait.
De meldingen die ik van ODBC krijg zijn bv:
[Microsoft][SQL Native Client]Named Pipes Provider: The specified network name is no longer available.
[Microsoft][SQL Native Client]Communication link failure

Ik heb een 'retry' mechanisme ingebouwd, dat controleert of het allemaal gelukt is, en als dat niet zo is, verbreken we de connectie en proberen we het opnieuw. Ik merk echter dat dit erg lastig af te dichten is... En het voelt als patchen en niet als een structurele oplossing.

Ik krijg steeds meer de indruk dat het een automatisch mechanisme ergens is, dat controleert hoe lang connecties open staan en ze vervolgens nekt.
Ik heb al een paar keer gelezen dat het best practice is om connecties vlak voor het uit te voeren statement te openen, en daarna direct weer te sluiten.
Ik kan echter tussen de 13 miljard hits niet een artikel hierover vinden met argumentatie waarom.

Volgende vragen heb ik:
- Heeft iemand dezelfde ervaringen : lang open connecties + spontane verbreking?
- Heeft iemand een referentie naar leesmateriaal dat dit onderbouwd als oorzaak?
- Als iemand DE oorzaak weet houd ik me aanbevolen.
- Praktische adviezen?

Achtergrond van de testomgeving.
SQLServer 2005 draait op andere machine dan de applicatie.
Naast de 2005 draait er ook een 2000 en een 2008R2 versie, maar de connectie is naar de 2005 versie.
Er draaien meerdere applicaties parallel op de applicatie'server' met het zelfde connectie mechanisme.
Op beide machines draait Windows XP

Waarom heb ik dit onder SE&A geplaatst? Omdat het om een architecturele aanpassing gaat als ik de applicatie moet aanpassen en overal 'connect' en 'close' moet gaan inbrengen...

[D-Raven]

Ik zou zeggen, ga je eens inlezen in connection pooling, dan kom je vanzelf argumenten tegen die aanhalen waarom connection pooling beter is dan een enkele long lived connectie.

Dat je verbinding spontaan verbroken word zal hoogstwaarschijnlijk je SQL server zijn die wat van zijn resources opruimt, of om andere redenen tegen een timeout loopt. Dat gezegd, dit kun je tevens configureren in de connection string dmv de property 'Connection Timeout'.

Praktisch advies: Ga nou niet je connectie string aanpassen om te proberen die timeout te voorkomen. Maar pas je code aan dat je fatsoenlijk gebruik maakt van connection pooling.
Daarnaast als je een batch start vanuit je applicatie welke 10 (!!!) uur duurt. Doe dat dan in meerdere stappen, ipv een grote. Bijvoorbeeld eerst je app het werk klaar laten zetten, en vervolgens een job op de server starten die aan de gang gaat? dan hoef je ook niet 10 uur lang je verbinding open te houden...

Als je dan je job aanpast dat hij ergens voortgang informatie wegschrijft, dan zou je dat periodiek kunnen queryen om te kijken wat de voortgang is.

[chime]

Connectie 10 uur openhouden ... leuk als omwille van 1 of andere reden een batch job meerdere keren wordt gestart en je connecties op de server beperkt zijn => kan het zijn dat productie in 1 keer niet meer aan hun data kan.

[o_f_course]

D-Raven schreef op vrijdag 13 januari 2012 @ 10:19:
Ik zou zeggen, ga je eens inlezen in connection pooling, dan kom je vanzelf argumenten tegen die aanhalen waarom connection pooling beter is dan een enkele long lived connectie.

Hm, dacht dat ik al genoeg daarvan gelezen had. Argumenten gaan meer in de richting van voordelen van pooling, maar niet negatieve dingen van long lived connections. En dat laatste ben ik benieuwd naar. Ik ga wel verder met m'n zoektocht in die richting

Dat je verbinding spontaan verbroken word zal hoogstwaarschijnlijk je SQL server zijn die wat van zijn resources opruimt, of om andere redenen tegen een timeout loopt. Dat gezegd, dit kun je tevens configureren in de connection string dmv de property 'Connection Timeout'.

Ga ik doen.

Praktisch advies: Ga nou niet je connectie string aanpassen om te proberen die timeout te voorkomen. Maar pas je code aan dat je fatsoenlijk gebruik maakt van connection pooling.

Het lijkt soms wel een politieke strijd:
'nee, dit moet gewoon kunnen, misschien niet optimaal. probleem zit in SQLServer 2005'
'ja, het zou beter zijn om dit aan te passen'.
Ik probeer support bij het MT voor het laatste te winnen, maar het aanpassen gaat een aanzienlijke inspanning vergen: meer dan 200 plaatsen worden geraakt. Ik heb een centrale cache die de connectie bewaard, maar die moet in een 'factory' worden omgezet en na afloop moet de connectie worden gesloten.

Raar is iig dat we bij SQLServer 2000 dit probleem niet kenden. met 2008R2 hebben we nog te weinig ervaring om te concluderen of het daar al dan niet ook gebeurd.
We vermoeden:
- rare instelling in de database (tempdb of de productiedb) (growth, etc)
- timeout instellingen ergens van SQLServer 2005 zelf
- andere architectuur van 2005 (alles gaat via tempDB, dat is niet het geval bij 2000, en ook niet bij 2008r2 als ik het goed begrepen heb).
- de druppel die de emmer doet overlopen ergens.
- bug in onze ODBC interface?

chime schreef op vrijdag 13 januari 2012 @ 12:12:
Connectie 10 uur openhouden ... leuk als omwille van 1 of andere reden een batch job meerdere keren wordt gestart en je connecties op de server beperkt zijn => kan het zijn dat productie in 1 keer niet meer aan hun data kan.

mbt aantal connecties hebben we nog geen last gehad van begrenzingen. in totaal gaat het om 30 connecties die open zijn. dat hoeft geen probleem te zijn. Meerdere zijn min of meer 'slapend', daar gebeurt niet veel meer mee. een paar zijn zeer actief.
Meerdere omgevingen gelijktijdig (dus bv 2x 30 connecties) gaat ook zonder problemen.

Overigens: de batch is al in stukken gehakt: het bestand dat verwerkt moet worden (vo107 GBA import bestand als dat wat zegt) betreft ca 5 GB, en dat kon men niet in z'n geheel aanleveren. Daar heeft men nu 4 bestanden van gemaakt

We hebben intensievere batches (meer belasting voor SQLServer) die meer connecties openhouden, en langer draaien (>48 uur) zonder problemen op 2000...

[Verwijderd]

Heb een keer een probleem gehad met een firewall die sessies > 4 uur ging opschonen. Samen met onze netwerkbeheerders vrij snel kunnen oplossen.

Zou toch even een netwerkbeheerder even vragen of hij toevallig niet iets kan terugvinden in de logging rond het tijdstip dat jij het probleem ervaart. Misschien dat hij sowieso iets kan uitsluiten?

Wat wel raar is dat dit niet optreedt in SQL 2000? Draait deze toevallig op een andere machine dan de 2005 instance? Kan me niet voorstellen dat dit door SQL 2005 wordt veroorzaakt. Heb ik nog nooit problemen mee gehad in ieder geval.

Nou weet ik niet hoe jouw batch proces in elkaar zit dus is het lastig aan te geven welke stappen ik zou volgen. Ik zou waarschijnlijk proberen om het probleem te reproduceren zonder dat ik hiervoor het batch proces zou moten draaien. Kan je niet een klein stukje programmatuur maken (op dezelfde app machine) welke eens in het kwartier een record insert in een tabel met twee kolommen: tijdstip (getdate()) en de duur dat het proces nu draait?

[BtM909]

Dit probleem komt juist voor in SQL2005 en hoger, omdat MS steeds meer functionaliteit heeft toegevoegd om de database omgeving performant te houden. Het zal me niet verbazen, wat Deathraven zegt, dat de database om de zoveel tijd de connectie verbreekt, juist omdat ze slapend zijn.

Ram eens een profiler op je connecties, als het vanuit MSSQL komt, dan zal je daar de disconnect reason voorbij zien komen.

[Verwijderd]

Het spijt me hoor maar dit probleem ben ik echt nog niet tegen gekomen met zowel SQL 2000, 2005 als 2008. Denk je echt dat als dit probleem zou bestaan in SQL 2005/2008 dat Microsoft niet allang een patch zou hebben uitgebracht? Ik beheer een groot aantal SQL server databases welke op allerlei manieren via een centrale machine worden gemonitord, maar echt nog nooit een dergelijk probleem tegen gekomen welke is veroorzaakt door SQL Server.

Als de connectie al door SQL server zou worden verbroken zou je toch in ieder geval een melding verwachten in de Errorlog. Misschien dat je die sowieso is kan raadplegen, maar ik verwacht eigenlijk dat je hier niets in gaat vinden.

[RobIII]

Verwijderd schreef op zaterdag 28 januari 2012 @ 00:35:
Het spijt me hoor maar dit probleem ben ik echt nog niet tegen gekomen met zowel SQL 2000, 2005 als 2008. Denk je echt dat als dit probleem zou bestaan in SQL 2005/2008 dat Microsoft niet allang een patch zou hebben uitgebracht? Ik beheer een groot aantal SQL server databases welke op allerlei manieren via een centrale machine worden gemonitord, maar echt nog nooit een dergelijk probleem tegen gekomen welke is veroorzaakt door SQL Server.

Het spijt mij ook maar als je een beetje bekend bent met SQL Server weet je ook dat hier de enige overeenkomst is dat jij SQL server(s) hebt en TS ook. Je hebt verder geen enkel vergelijkingsmateriaal over geheugenconfiguraties, grootte van database(s), aantal concurrent gebruikers en andere "resource pressure". Ook weet je niet, of geef je dat althans niet aan, of die monitoring tool 24/7 connecties open houdt of bij elke "poll" een nieuwe connectie op zet of niet en ga zo maar door. Je hebt, zoals ik zei, maar één overeenkomst (e.g. "SQL Server"); op basis daarvan kun je nou niet bepaald uitspraken doen.

Wie zegt dat dit gedrag gepatched zou moeten worden; het is misschien wel behaviour by design (en die kans acht ik groter; evenals de kans dat er een bug/fout in TS's software, netwerk, firewall, whatnot zit).

http://blogs.msdn.com/b/s...ve/2006/03/09/546852.aspx

Neemt niet weg dat ik 't ook maar pas een keer of 2 ben tegen gekomen; ik vermoed dat in veruit de meeste gevallen inderdaad zaken als een firewall oid in de weg zitten.

D-Raven schreef op vrijdag 13 januari 2012 @ 10:19:
Dat gezegd, dit kun je tevens configureren in de connection string dmv de property 'Connection Timeout'.

Neen.

quote: http://blogs.msdn.com/b/a...ve/2004/09/20/231963.aspx

I have found that it is really hard to explain what this keyword does. Here is some pseudo code:

code:

On SqlConnection.Close
Check if time the connection has been open > Connection Lifetime
   throw the connection away
Else
   Put connection on the pool

This is the _only_ time that we will check Connection Lifetime. We do not check the lifetime when the connection is idle in the pool and we certainly don't use this value to determine how long it can stay in there.

D-Raven schreef op vrijdag 13 januari 2012 @ 10:19:
Praktisch advies: Ga nou niet je connectie string aanpassen om te proberen die timeout te voorkomen. Maar pas je code aan dat je fatsoenlijk gebruik maakt van connection pooling.

Dat is dan wél weer correct

quote: http://blogs.msdn.com/b/a...ve/2004/09/20/231963.aspx

Summary: Do not use Connection Lifetime unless you are connected to a cluster and understand the trade offs.

[ Voor 46% gewijzigd door RobIII op 28-01-2012 01:47 ]

[Verwijderd]

RobIII schreef op zaterdag 28 januari 2012 @ 01:18:
[...]

Het spijt mij ook maar als je een beetje bekend bent met SQL Server weet je ook dat hier de enige overeenkomst is dat jij SQL server(s) hebt en TS ook. Je hebt verder geen enkel vergelijkingsmateriaal over geheugenconfiguraties, grootte van database(s), aantal concurrent gebruikers en andere "resource pressure". Ook weet je niet, of geef je dat althans niet aan, of die monitoring tool 24/7 connecties open houdt of bij elke "poll" een nieuwe connectie op zet of niet en ga zo maar door. Je hebt, zoals ik zei, maar één overeenkomst (e.g. "SQL Server"); op basis daarvan kun je nou niet bepaald uitspraken doen

Als je maar een beetje bekend met SQLServer. Laat ik dit maar niet verkeerd opvatten. Ik weet wel beter. IK mag toch gewoon zeggen dat ik vanuit mijn ervaringen dit nog nooit ben tegengekomen? Ik zeg toch ook nergens dat het niet kan voorkomen? Ik geef alleen aan dat ik niet verwacht door het door SQL 2005 of hoger wordt veroorzaakt.

Mijn monitoring tool....houdt inderdaad geen connectie open. Die pollt iedere keer opnieuw. Wat niet betekend dat ik geen processen op een database heb draaien die al meer dan 10 uur een connectie hebben met de database.

Wie zegt dat dit gedrag gepatched zou moeten worden; het is misschien wel behaviour by design (en die kans acht ik groter; evenals de kans dat er een bug/fout in TS's software, netwerk, firewall, whatnot zit).

http://blogs.msdn.com/b/s...ve/2006/03/09/546852.aspx

Neemt niet weg dat ik 't ook maar pas een keer of 2 ben tegen gekomen; ik vermoed dat in veruit de meeste gevallen inderdaad zaken als een firewall oid in de weg zitten.

Behaviour bij design? Als dit behavour bij design is zou ik van je verwachten dat je echt een artikel oplevert die dit beschrijft en niet een artikel uit 2006 die de nieuwe keep-alive optie bespreekt van 2005.

Ik snap eigenlijk niet waarom je reageert op mijn post terwijl je geen voorstellen aandraagt om het probleem te kunnen aanpakken? Daar gaat het hier toch om? Ik zie je namelijk helemaal niet ingaan op het voorstel wat ik doe om het probleem te reproduceren Even een klein stukje programmatuur maken die om de zoveel tijd (instelbaar!) een record wegschrijft in de database met de systeemdatum en de tijd dat het proces al draait. Eventueel zou je nog kunnen toevoegen dat zodra de connectie is verbroken naar de database dat je een ping uitvoert naar de databasemachine om te kijken of deze eventueel te benaderen is. Wanneer je het op deze manier kunt reproduceren ben je veel sneller in staat om het probleem te kunnen tackelen. Zelfs als dit eventueel zou liggen aan SQL 2005 of hoger.

Als dit het probleem niet reproduceert profiler gaan gebruiken zoals al eerder voorgesteld of beter nog dan gelijk met Wireshark aan de haal gaan. Zorg wel dat je weet wat je doet met Wireshark. Genereert erg veel logging als je hem niet goed instelt.