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

Hi,

This has been discussed in 1-2 threads here and is documented in our Access blog where we track current problems and bugs.

So far, we haven't found a reliable reproduction scenario. Nevertheless I'll report the problem again to the Microsoft Access team now, because the reports here are starting to pile up.

Servus

Karl

Answer 2

Hi Mike33511,

You could be your own worst enemy, with statements like this:

> ...I have also run a SQL Shrink Database.

Running CheckDB in SQL Server and compacting your Access FE file is fine. But shrinking a SQL Server database is rarely needed and will introduce fragmentation problems.

Why? Because shrinking a SQL Server database is known to cause index fragmentation. As a result, any data read operations are going to need to read more 8 KB pages into memory, just to read the same about of data, therefore adding more memory pressure. SQL Server only reads complete pages of data; it cannot read 1/3 or 1/2 of an 8 KB page of data. If your data is spread out in more pages, due to fragmentation, you are increasing CPU and I/O requirements, as well as memory required to read the additional pages into RAM.

What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE?

https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/

Here is a case when shrinking is okay, but the DB sizes mentioned are much larger than what your SQL Express can handle (10 GB limit):

What If You Really DO Need to Shrink a Database?

https://www.brentozar.com/archive/2020/07/what-if-you-really-do-need-to-shrink-a-database/

A quick note about the second article linked above. In the section titled "Don’t shrink. Do this instead:", item #2 reads:

"Move objects to the new filegroup using CREATE INDEX…WITH DROP_EXISTING = ON, ONLINE = ON commands"

I'm fairly certain that the free SQL Express does not support any ONLINE=ON actions. So, you will need a short outage window to do this step.

Tom Wickerath

Microsoft Access MVP Alumnus (2006 -- 2012)

Answer 3

I have checked the Access Blog and the last update I see was on December 19th. Any updates on this issue? Are you still getting reports of this issue, have you been able to recreate it? We are still having the out of memory error. Please advise.

Answer 4

Also note the database is connecting with 'SQL Server Native Client 11.0'. I have also tried 'ODBC Driver 17 for SQL Server' and the 'SQL Server' drivers and have the same issue with all three.

Answer 5

No progress yet, then.

We've refreshed the request for the Access team to look further into this. The reports are sort of scattered, so a pattern is not readily apparent, unfortunately.