Run-time error 3035 system resources exvceded

Ik heb een grafiek gemaakt die elke 1 minuut de onderliggende query draait en dan de update hiervan laat zien.
Ca. elke minuut wordt er data in een SQL tabel geschreven en deze SQL tabel benader ik via een Pass Trough Query.

Als ik de App start loopt het zonder problemen voor een poos, ca 1 uur, maar na verloop van tijd komt er de "Run-time error 3035".

Heeft iemand een idee waar ik live kan zien welke "systeem recources" overschreden worden?
Kan ik deze recources aanpassen zodat het meer aankan?

Ik heb in mijn data al gekeken, maar sluit elke RecordSet aan het einde keurig af. (voorbeeld van code)

Dim rs as DAO.Recordset 'Declaratie in het Option Compare Database deel

Function ToonLocatie()
x = 1

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Locatie FROM LocatieTrendQ ORDER By Locatie ASC")

Me.frmLocatie = ""
Do Until rs.EOF
Me("TglLo" & x).Caption = rs("Locatie")
Me("TglLo" & x).ForeColor = Zwart
Me("TglLo" & x).HoverColor = Groen
rs.MoveNext
x = x + 1
If x = 6 Then Exit Do
Loop
'Set rs = Nothing
rs.Close << Afsluiten van de RecordSet zodat er niets open blijft staan.

Do Until x = 6
Me("TglLo" & x).Caption = ""
Me("TglLo" & x).ForeColor = Blauw
Me("TglLo" & x).HoverColor = Blauw
Me("TglLo" & x).Visible = True
x = x + 1
Loop

End Function


Kan iemand mij een idee geven waar ik naar kan kijken of wat ik kan ondernemen op dit probleem op te lossen.

Deze applicatie is om eigen lijk 24/7 als een dashboard op een scherm te laten staan en het is na een paar selecties eigenlijk niet de bedoeling dat iemand er iedere 10 to 60 minuten iets mee doet.

Misschien lees ik je "functie" niet goed, maar volgens mij doet die per saldo niets. Het resultaat van de eerste loop wordt in de tweede loop direct weer ongedaan gemaakt en die tweede doet elke keer hetzelfde. Daar zou ik ook mee kappen

Het is niet de functie die werkt prima het ging mij er om om aan te geven dat ik de rs.Close heb opgenomen in al functies waarbij dit eerder geset wordt.
Dit om te voorkomen dat deze opene blijven staan en geheugen in beslag blijven nemen.

Misschien dan toch maar Set rs = Nothing gebruiken?

Zie https://stackoverflow.com/questions...between-rs-close-vs-rs-nothing-in-a-recordset

Ik heb alle rs.Close naar Set rs = Nothing aangepast.
Het lijkt wel langer goed te gaan, maar ik zie dat de grafiek telkens meer tijd neemt voor dat deze geüpdatet is.
bij rs.Close kreeg ik de foutmelding al na ca. 30 minuten en met Set rs = Nothing duurt het ca. 60 minuten voor dat de foutmelding komt.

Heeft iemand een idee hoe ik er achter kan komen o.b.v. welke data de recources vollopen?

Zou iets te maken kunnen hebben met de Nieuwe style grafieken in Access
Is er een mogelijkheid om deze bijvoorbeeld te legen

Nog wat extra info:

Als ik de PC een herstart geef en Access opstart en dan in Task manager kijk dan neemt Access bij de start 148 MB aan geheugen.
Tijdens het laten draaien van de App zie ik het geheugen gebruik toenemen.
Na iedere verversing van de grafiek loopt het geheugen gebruik op van 207,4 MB, 216,3 Mb, 221,4 Mb en dan naar 228,4 MB. Dus elke minuut ca. 7MB erbij.
Het CPU gebruik voor Access is eens per minuut 10% en dan direct daarna weer 0%.

Voor het gemak heb ik de App even bijgesloten.
Als je frmTrend start dan selecteer je locatie 3 en laat het systeem de grafiek zien van de laatste 60 minuten.
Door op de knoppen onder range te drukken kan het bereik aangepast worden naar 120 of zelfs to 1020 minuten.

Wordt lastig als we het hele verhaal niet kennen en niet weten waar het precies vastloopt.

Probeer eens om de odbc tabellen als access tabellen in je voorbeeld te zetten, dan kunnen we er wat mee. Nu natuurlijk helemaal niets. Net zo als het tweede deel van je functie overigens; je hebt dus twee routines die in een lus lopen op dezelfde x waarde. In de eerste lus verhoog je die tot hij de waarde 6 heeft. Dat snap ik nog. Vervolgens start je de tweede lus, die je laat lopen tot x 6 is. Maar die waarde heb je al bereikt, want dat was de eindvoorwaarde van je eerste lus! Dus de tweede wordt echt niet uitgevoerd. Dat hij het effect van de eerste lus neutraliseert, maakt dus niet uit, want hij wordt tóch niet uitgevoerd! Sowieso vind ik het een rare lus, die ik dan toch zo zou doen:


De lus op basis van Do Until rs.EOF heeft alleen zin als je minder dan 6 records hebt in de query. Als er altijd meer in de recordset zitten, dan is mijn oplossing dus handiger. En om het tweede deel te laten werken moet je op zijn minst één regel extra toe voegen:


Maar goed, je maakt dus de eerste lus overbodig. What's the point of that?

Daarnaast heb je deze twee regels verkeerd staan (dit is dus correct)
En ik gebruik ze eigenlijk nooit, want ze zijn overbodig: zodra je een functie beëindigt, wordt de recordset uit het geheugen verwijderd. Je hoeft dat dus zelf niet te doen, en het zou ook niets in tijd uit mogen maken. Het verwijderen gebeurt immers al .

Kijk eens naar het geheugen gebruik en het aantal open SPID's op de SQL server. Volgens mij sluit je de processen op de server niet correct af en ben je de SQL server aan het wurgen. Dit soort applicaties worden daarom meestal niet toegelaten op een data server. Je kan de reporting services gebruiken om je grafiek te draaien, maar ook daar zou ik niet om de paar minuten refreshen. Laat ook eens een trace draaien op de data server en bekijk het query plan van de query die om de minuut de data ophaalt. Aangezien het toch om een reporting query gaat zou ik die zeker met isolation level readuncommitted laten draaien of de query hint with(nolock) meegeven in je passthrough query.

Ik heb met de Task manager open eens gekeken waar het geheugen omhoog springt als ik er in stap mode door heen ga.

Achter de regel heb ik tussen <> aangegeven wat de geheugenhoeveelheid is na die regel.



Sub RequeryForm() <190,4 MB> Start.

rRange = Me("TglRng" & [frmRange]).Caption
rrFilter = rFilter & " And Range < " & rRange

If rFilter = "" Then
Me.TrendGrafiek.RowSource = "SELECT REFW, Gemm, Range FROM TrendQ"
Else

Me.Filter = rrFilter <194,7 MB>
Me.FilterOn = True <195,2 MB>

TrendGrafiekSetup >> Jump naar sub routine

Me.TrendGrafiek.RowSource = "SELECT REFW, Gemm, Range FROM TrendQ WHERE " & rrFilter & "" <200,5 MB>
'Me.TrendGrafiek2.RowSource = "SELECT OnderREF, BovenREF, RestREF, Range FROM TrendQ WHERE " & rrFilter & ""

End If

End Sub
Sub TrendGrafiekSetup()

Set rs = CurrentDb.OpenRecordset("SELECT REFW, Gemm, Product, Eenheid, Aantal, NoGap, Afkeur, Ex_Ng, Tu1p FROM TrendQSubREF WHERE " & rFilter) <198,4 MB>

rs.MoveLast

GrafMin = rs("REFW") - 5
GrafMax = rs("REFW") + 5

If IsNull(GrafMin) Then
GoTo JumpREFW
End If
If IsNull(GrafMax) Then
GoTo JumpREFW
End If

'Pas de vertikale schaal van de grafiek aan.
Me.TrendGrafiek.PrimaryValuesAxisRange = acAxisRangeFixed
Me.TrendGrafiek.PrimaryValuesAxisMinimum = GrafMin
Me.TrendGrafiek.PrimaryValuesAxisMaximum = GrafMax
Me.TrendGrafiek.SecondaryValuesAxisRange = acAxisRangeFixed
Me.TrendGrafiek.SecondaryValuesAxisMinimum = GrafMin
Me.TrendGrafiek.SecondaryValuesAxisMaximum = GrafMax

'Toon de actuele gegevens in de knoppen onder de grafiek

If rs("REFW") > rs("Gemm") Then 'Gemmidelede lager dan Ref Gewicht
Me.ShwGemm.BackColor = Oranje
Me.ShwOverVul.BackColor = Oranje
Else
Me.ShwGemm.BackColor = Blauw <198,5 MB>
Me.ShwOverVul.BackColor = Blauw <198,6 MB>
End If

If rs("Tu1p") > 1.5 Then 'Tu1% meer dan 1,5%
Me.ShwTu1_.BackColor = Oranje
Else
Me.ShwTu1_.BackColor = Blauw <198,7 MB>
End If

If (rs("Afkeur") / rs("Aantal")) > 0.1 Then 'Maar dan 10% afkeur
Me.ShwAfkeur.BackColor = Oranje
Else
Me.ShwAfkeur.BackColor = Blauw <198,8 MB>
End If

If (rs("NoGap") / rs("Aantal")) > 0.05 Then 'Meer dan 5% No-Gap
Me.ShwNoGap.BackColor = Oranje
Else
Me.ShwNoGap.BackColor = Blauw <199,0 MB>
End If

If rs("Ex_Ng") > 10 Then 'Meer dan 10 stuks Extern afkeur
Me.ShwEX_NG.BackColor = Oranje
Else
Me.ShwEX_NG.BackColor = Blauw <199,1 MB>
End If

'Het verzorgen van de gradatie verloop op de knoppen
Me.ShwGemm.Gradient = Verloop <199,2 MB>
Me.ShwOverVul.Gradient = Verloop <199,3 MB>
Me.ShwTu1_.Gradient = Verloop <199,4 MB>
Me.ShwAfkeur.Gradient = Verloop <199,5 MB>
Me.ShwNoGap.Gradient = Verloop <199,6 MB>
Me.ShwEX_NG.Gradient = Verloop <199,7MB>

Eenheid1 = Trim(rs("Eenheid")) 'Trim de spaties van de eenheid

Me.ShwProduct.Caption = rs("Product")
Me.ShwGemm.Caption = Round(rs("Gemm"), 3) & " " & Eenheid1
Me.Shwn.Caption = rs("Aantal")
Me.ShwTu1_.Caption = rs("Tu1p")
Me.ShwAfkeur.Caption = rs("Afkeur")
Me.ShwNoGap.Caption = rs("NoGap")
Me.ShwEX_NG.Caption = rs("Ex_Ng")
Me.ShwOverVul.Caption = Round(((rs("Gemm") / rs("REFW")) - 1) * 100, 3) & " %" < 200,2 MB>

JumpREFW:


Set rs = Nothing
'rs.Close

End Sub


Als ik rs.Close onder Set rs = Nothing zet dan krijg ik een fout melding "Run-time error 91: Object variable or With block variable not set.
Als ik rs.Close boven Set rs= Nothing dan gaat het wel goed. Het maakt echter niets uit in het geheugen gebruik.

Resource problemen van de database server kan je moeilijk spotten via de task manager. Voor de meeste database systemen wordt er een vaste geheugenpool toegekend aan de database server, dus in de task manager zal je bv. zien dat de SQL server altijd XXX GBytes geheugen gebruikt. Dus moet je de gegevens opvragen in de database zelf. Meestal heb je wel een grafisch programma zoals SSMS waar je de statistieken kan opvragen.

Voor SQL Server is de Page Life expectancy (PLE) een goede graadmeter. Zelf hou ik die liefst boven de 1000 seconden. https://blog.sqlauthority.com/2010/12/13/sql-server-what-is-page-life-expectancy-ple-counter/
In jou geval zou ik zeker kijken hoeveel processen er open staan en kijken of deze steeds verhogen. Dit zou aangeven dat de processen geopend worden, maar niet correct gesloten. In het geval van SQL server kan je daarvoor de sp_who2 procedure gebruiken, die toont de actieve processen. Normaal heb je +/- 50 systeemprocedures, SPID's > 50 zijn gebruikers processen .
Verder kan je (vanaf SQL 2016) zien welke queries de meeste CPU/geheugen verbruiken in de Query store.

RobertJB66 zei:

Als ik rs.Close boven Set rs= Nothing dan gaat het wel goed. Het maakt echter niets uit in het geheugen gebruik.

Klik om te vergroten...

Mijn opmerking was correct, de geplakte code was nog die van jou, niet goed geplakt dus . Dat het niets uitmaakt voor het geheugengebruik maakt uiteraard niets uit, want dat heb ik je óók uitgelegd. Ik denk dat de opmerkingen van Noëlla behoorlijk hout snijden. Dus dat je het aan de server kant moet oplossen, niet aan de cliënt kant.

En doe ons een lol, en gebruik de CODE TAG .

Ok even het e.e.a. in SQl bekeken.

Met de onderstaande Query het aantal processen bekeken:

het aantal staat tussen de 40 en 53.


Ook exec sp_who2 laat het zelfde aantal zien

Met de onderstaande Query het aantal SPID



Bij 181 komt de foutmelding in Access.

Ik stuit Access af en start het opnieuw op. Ik heb Access dan even op 10 seconde gezet zodat het wat sneller gaat.

Als ik dan regelmatig op de SPID query druk dan zie ik dat deze op 181 start en dan bij elke verversing met 1 of 2 verhoogd.
Nu dat ik dit tik staat de teller al op ruim 400.

De proces blijven op ca. 52 staan.


SPID zou dus wel eens de boos doener kunnen zijn.
Een suggestie hoe ik in Access dit dan juist kan en moet afsluiten

Is dit wellicht een oplossing?

Ik heb de applicatie opnieuw gestart en kijk direct in SQL.

de cntr-value blijft in eerste instantie laag, maar gaat daarna oplopen.
Als ik even wacht en dan weer de query uitvoer om te zien wat de waarde is dan springt hij van 118 terug naar 40.
Als dan daarna weer de cntr_value op blijft vragen dan blijft deze oplopen.

Wat mij opvalt, is als ik het front-end deel stop, het Form is dus gesloten, loopt niet meer, dan stopt de geheugentoenamen in Task manager, maar de cntr_value blijft wel gewoon oplopen als ik het afvraag!

Ook als het front-end (client) vast loopt omdat het onvoldoende recources heeft, blijft het cntl_value aantal oplopen

Het aantal SPID komt niet boven de 60 uit.

Als ik de app afsluit en weer opnieuw start dan gaat het cnrt_value aantal gewoon door ruim over de 2.100 heen zonder dat ik een foutmelding krijg bij de client. Hiervan het de gebruikte geheugen hoeveelheid weer gestart op ca. 138 MB.
De App blijft het doen tot dat deze ca. 1 Gb aan geheugen heeft gealloceerd. Samen met de andere taken die er dan draaien is dan tot ca. 92% van het geheugen gebuikt.
Ik zeg niet dat dit echt de oorzaak is ik probeer een ieder van zoveel mogelijk info voorzien zodat jullie mij zo goed mogelijk kunnen helpen.:d:d

Kort voor dat de client het probleem geef springt het cntr-value aantal naar 17 en begint dan weer op te lopen.


Is er een methode in VBA om geheugen die de client alloceert weer vrij te geven?

Dat is alleen de oplossing als je de database helemaal de soep wil indraaien.

Wat je ziet als het aantal SPID's steeds verhogen is dat de applicatie steeds nieuwe processen opent en geen enkele correct sluit. Het max aantal locks verhogen zorgt er alleen voor dat je meer processen tegelijk kunt openhouden, dus de error uitstelt. Wat je moet doen is de applicatie herschrijven zodat het niet steeds nieuwe processen gaat openen, of de processen ook effectief gaat afsluiten.

Wat je kan doen is een procedure op SQL server schrijven die de gegevens ophaalt met een NOLOCK hint. Beter is om de grafiek in SSRS te maken (isolation level readuncommitted), dan kan je die via een website verdelen. En zeker niet elke minuut refreshen

Ik ben ook geen voorstander van het verhogen van zaken want dat is uitstel van executie.

Ik heb het vermoeden dat NoellaG reactie is op het bericht van OctaFish.

Als ik kijk op de SQL server dan lijkt het erop dat die niet volloopt.

De grote vraag is dan eigenlijk wat zou ik kunnen doen om er voor te zorgen dat Access niet bij iedere verversing niet ca. 7 MB aan geheugen alloceert.

De boosdoener is wellicht de ODBC connectie en de ADO recordset die je telkens opent. Een ADODB cmd object is een iets nieuwere techniek (ook al aanwezig van het jaar 2000) en gaat iets beter met geheugen om dan ADO. Je vernietigt de recordset wel in access, maar grote kans dat de transactie op SQL server gewoon blijft openstaan. Als je geen with(nolock) gebruikt en readcommitted werkt (=standaard isolatie level), dan legt deze query (shared) locks op de records. Een minuut later kom je al terug met dezelfde query, waarschijnlijk voor dezelfde data en moet even wachten op de server vanwege de locks. Waarschijnlijk is dit een Shared lock, dus, na een controle dat niemand deze gegevens wil wijzigen, mag de tweede query lezen. Een minuut daarna komt al de derde query, weer voor dezelfde gegevens. Die moet iets langer wachten, enz.... . Als er een verzoek komt om de gegevens te wijzigen zit alles pas goed vast.
Als de transactie bovendien niet goed afgesloten wordt, blijft elke query in het geheugen zitten en zal de PLE steeds verder dalen tot deze onder de 100 daalt en de server bij PLE = 0 een fatal exception genereert.
Dus ik zou in elk geval readuncommitted werken of met een nolock hint. Dat zal via een passthrough query of een procedure moeten, want die optie bestaat niet in Access recordsets.

Bekijk ook op de server het query plan van de uiteindelijke query op de server( die kan via een trace opgehaald worden) en indien nodig, maak de correcte index(en) aan.

Bekijk ook de mogelijkheid om je grafiek, die in feite een rapport type is, via de reporting services van SQL server zelf aan te maken, dan ben je van die ODBC connectie af.

Ik hebben een versie gemaakt waarbij de tabellen in access zitten en een versie waarbij ik de tabellen via ODBC link met de Access front-end App.

In beide gevallen als ik de App start dan wordt er geheugen in beslag genomen, maar deze komt na ca. 3 minuten niet hoger dan 200 MB.
Ik heb de ververssnelheid op 5 seconde gezet om het proces een beetje snel te laten verlopen.

Bij deze test wordt er geen extra data in de SQL database geschreven voor de ODBC gekoppelde tabellen en als de data in de Access App zit dan wordt er ook geen extra data in de tabellen geschreven.

Als ik de ODBC gekoppelde versie start maar nu laat ik wel iedere minuut data toevoegen aan de SQl database dan zie ik dat het geheugen gebruik oploopt tot ca. 270 MB en blijft dan min of meer stabiel.

Het moet dus wel zeker iets te maken hebben met de Pass through Query.

Ik voor de Pass Through Query gekozen om een probleem dat ik eerder tegen kwam op te lossen echter bij dit deel van de App lijkt de ODBC koppeling geen probleem op te leveren

Ik test nog wat verder voor nu in ieder geval bedankt voor het meedenken.

Die verver frequentie had ik voor testen op 1 minuut staan.
Ik heb hem nu voor een duur test op 5 minuten gezet en dan geeft het geen enkel probleem :d.
Nog even wat door testen om te zien waar de sweetspot zit.

Ik hoop dan maar dat er geen andere applicaties op die server moeten draaien, want die werkwijze blijft enorm veel resources vreten van de SQL Server. Dat is de reden dat in de meeste bedrijven op SQL server rechtstreekse verbindingen naar Office toepassingen niet toegelaten worden.