MS Access Memory Issue

Question

Programming in Access 365 but it is a 32-bit access front end with a SQL Express backend. Been using this database for years with no issues. All of a sudden starting approx. last Monday 12/12/2022 we started getting out of memory errors on the client side. Users started getting it randomly over 2 or 3 days as if it was an update.

If I open the database and watch task manager, as I open forms and close them, I see the memory usage go up when the form is opened as expected but it never goes back down to where it started from when the form is closed. We have a few form timers that run code every 2 to 5 minutes and the same thing happens, memory usage goes up when the timer triggers but when the code finishes the memory is not released.

I have run CHECKDB on the SQL Express tables, I have also run a SQL Shrink Database. On the front end, I have decompiled the database, run a compact and repair and then recompiled it.

I have been able to reproduce the issue on a computer at my house with a different access database. This database is a 64-bit database with access tables not SQL. The same thing happens, when I open forms then close them the memory usage does not return to the amount before the form was open, always just a little higher until I get the error message.

Answer 1

I might have something. I found a table where I was seeing a memory spike when it opened. It contains several large data fields where paragraphs of information are entered.

I made a simple database with the table (inside the accdb) and when I run a make table query memory usage spikes from 34 to 48 mb. Running a second time the memory stays around the same.

Tried it on my computer without the office update and the memory maybe went up 1 meg.

Karl, should I send it to you? If yes send me the email address and I will zip it.

Answer 2

Hi,

I'm not sure if the example helps and it therefore makes sense to pass it on. It doesn't look like a "reproduction" scenario to me, as neither SQL Server is involved, nor the processes of the real problem cases, nor the error message, or are they?

As written in the other thread, the error message usually often happens due to lack of virtual memory (not necessarily RAM). Have you checked this? You can do that with the tool VMMap from Microsoft that you can run in parallel with your real problem scenario. Maybe it helps to find out what kind of memory problem it is and with which processes or amounts of data related to SQL Server it occurs.

Servus

Karl

Answer 3

Karl, as of now we don’t know where or what the issue is. We are using sql server but I’m not sure that is the issue. The process I put together was to show memory usage growth running a simple make table query with that office update. It doesn’t increase memory usage without the update. The issue seems to happen when I run processes with a lot of queries so think this query plus another 20 running which causes it to use up all ram.

My programs are 100-300 meg so tough to give you the exact replica.

I will look into the virtual test. Let me know your thoughts.

Answer 4

The consistent thing we have seen in similar reports of memory problems all include using SQL Server backends, so that definitely is significant.

When MS wants to investigate such reports, the most efficient way to proceed is to supply them with a clear replication scenario that can help focus on the issue. Otherwise it could be an extensive, time-consuming discovery process.

Answer 5

Hi,

I can also only guess so far, but since you asked for my thoughts:

My impression is that since the December update there has been no general increase in reports of memory problems, but only a good handful of reports in connection with SQL Server backends. If it were a general Access problem, I would expect more reports from Access-only users, who are in the vast majority.

Therefore, I think that "generalising" away from SQL Server is unlikely to take things further, and runs the risk of dispersal, unless there are palpable similarities in the processes, data volumes, symptoms, error message. Usually it's better to focus as specifically as possible on existing problem environments and cases.

In the other discussion here last week, affected poster Stuart Turner1 wrote that he could reproduce it consistently. Unfortunately, he did not answer again when I asked back. A reproduction scenario, a SQL script, even remote access to a consistent reproduction etc. This kind of things we would need for Microsoft, because for them, too, the scope of the problem is unclear so far, which makes it difficult to investigate.

Servus

Karl