System Resource Exceeded - on Compact and Repair - DISASTER PLEASE HELP

[wcrimi]

I have a disaster on my hands.



I was doing my weekly update of my database and everything was going fine. As part of it I manually delete some records from a table and then do a Compact and Repair. After I did that, I noticed that one of my tables was missing and I had a new table created called MSysCompactError. I don't recall the error that was in it. I ran the Compact and Repair again and it deleted another table. At that point I knew I had a major problem on my hands. I downloaded a backup from 2 weeks ago and started restoring.

Then the same thing happened when I did a Compact and Repair. I got an error and it started deleting tables and queries.

The latest error message in the MSysCompactError was

Error Code -1011
System Resource Exceeded
Error Table RS3b3

just a note ***Query RS3b3 was deleted as were other tables

The database itself is using 1,319,616 KB. I have other databases that are larger and work fine, but this specific one is very complex with a lot of tables and a LOT queries. Perhaps the complexity is causing an issue in the Compact and Repair???

I have all the data required to pick up where I left off 2 weeks ago in that backup, but since the problem recurred I don't know where to go from here.

PLEASE help.

One small possibility is to break this database up functionally into 2 databases. It would be a huge job to get it all right, but it might be a last resort ASSUMING THAT WOULD EVEN WORK.

[Mickjav]

Use the backup, is this the front end, Is the file spilt data and front end?

Edit: I have a system that uses 15 data files it contains a total of nearly 18 million enties.

[wcrimi]

Use the backup, is this the front end, Is the file s[ilt data and front end?

I downloaded the 2 week old backup, did a Compact and Repair on it, and it seemed fine.

Then I added the new data for the last 2 weeks (that worked fine). Then I did another Compact and Repair and it blew up again. The database is large, but well within the parameters which I believe is 2,000,000 KB. However, perhaps since there are a large number of complex queries the Compact and Repair is taking it over the system resource available anyway.

I am contemplating splitting the database into 2 by moving a lot of the data into another database and then linking to it, but that may be a fruitless and large effort if the problem something else. I do not know. That's part of the problem. I don't know what's going on or how to proceed.

[Mickjav]

So it's not a front end backend system Is it on a network if so what type of network I.E. Cable or Wifi??

[wcrimi]

So it's not a front end backend system Is it on a network if so what type of network I.E. Cable or Wifi??

It's a database located on my personal computer that I have backed up on Google Drive.

Virtually all the tables and queries I use are located within this database. There are just a few large tables linked in from other databases, but those are also on my personal computer. It's not a complex setup, but there are a LOT of queries and tables involved in the entire database.

[orange]

We often see issues with small/medium businesses. Can you step back and tell us more about your set up in plain English?
You have some large linked tables also on your PC. How do these get changed/updated?
Where and How do you do your backups? Just trying to get some context for your database and issues.

[Mickjav]

Can you post a empty copy of the data file(s)

mick

[wcrimi]

We often see issues with small/medium businesses. Can you step back and tell us more about your set up in plain English?
You have some large linked tables also on your PC. How do these get changed/updated?
Where and How do you do your backups? Just trying to get some context for your database and issues.

1. There are a couple of large tables in a database on my PC that get updated when I get some text files from an external source. I typically run an update query to update those every Monday morning.

2. There is a main Database with several tables on my PC that also get updated when I get text files from an external source. I typically run update queries to update those tables every Monday morning.

3. After that, there are a series of queries I run on Monday that use all this new external data to create new updated tables and reports on the main database.

This morning I was getting a head start on my Monday work and loaded all the external data into both databases. That went fine.

Also part of that Monday work I manually delete some records in one particular table in the main Database. I did that and it went fine. After I delete records, I normally run a Compact and Repair. I did that this morning to the main Database and that's when all hell broke loose. I noticed a MSysCompactError and noticed that at least one of my tables in that main database magically disappeared. I did it again and another table or some queries disappeared.

At that point I downloaded my latest backup 12/15/19. I brought it all back up to date and tried again. Same thing happened.

[Micron]

I suggest you open a new fresh db and import everything into it from the backup. Perhaps break this operation into sections such as tables first, then queries, etc. because of the system resources error issue. Then update the new db with the data. Back this one up before attempting a c/r on it.
Note that the file size limit is likely not the issue. It will be network related (dropping packets or connections) or other system resources. IIRC, during a c/r operation, Access creates a backup but temporary copy which gets removed when done. This could be corrupted if you run out of disk space or memory during the operation or Heaven forbid, are doing any of this over WIFI.

I get some text files from an external source

If you are downloading or doing anything from a cloud file, that's asking for trouble. If you first download the cloud copy to a local drive, that's OK. I mentioned that just in case.

[CJ_London]

that may be a fruitless and large effort if the problem something else

possibly, but something you should try. It is standard practice as recommended by MS to have tables in one backend db, everything else, including queries in another front end db. It should not take more than a couple of minutes to do. Take backups before starting. Take a copy of your db, open the copy, delete all the tables, then link to the original file. Then go to the original file and delete everything but the tables.

An alternative is to create a new db and then import the tables, create a second new db, link to the tables in the first new db, then import everything else

Other thing to check is if you are importing data which is causing a corruption - could be an illegal character is one of the files for example.

I had a new table created called MSysCompactError. I don't recall the error that was in it.

always helpful to make note of the error(s).

[wcrimi]

I suggest you open a new fresh db and import everything into it from the backup. Perhaps break this operation into sections such as tables first, then queries, etc. because of the system resources error issue. Then update the new db with the data. Back this one up before attempting a c/r on it.
Note that the file size limit is likely not the issue. It will be network related (dropping packets or connections) or other system resources. IIRC, during a c/r operation, Access creates a backup but temporary copy which gets removed when done. This could be corrupted if you run out of disk space or memory during the operation or Heaven forbid, are doing any of this over WIFI.

If you are downloading or doing anything from a cloud file, that's asking for trouble. If you first download the cloud copy to a local drive, that's OK. I mentioned that just in case.

I suspect I am hopelessly doomed.

I broke the problem database into 2 parts by removing some of the larger tables and putting them into their own database and then linking them into the primary one. I am still getting all sort of odd errors when I try to do a Compact and Repair on the original database.

I'm pretty close to despair at this point.

Some of it is surely salvageable, but close to 5 years of work to automate much of what I do may be gone.

[Micron]

I am still getting all sort of odd errors when I try to do a Compact and Repair on the original database.

That wasn't my suggestion (nor the suggestion of others if I interpreted them correctly).
The suggestion was to import everything into a new db (db A) - then back that up (copy of A) - then try to c/r db A and leave the old one alone. You should not have "removed" anything from anywhere if that's what you did.

[Mickjav]

To be honest I don't think he knows what he wants to do a lot of suggestions have been offered but he seems to skirt around them without taking them on board.

I did suggest he export all blank tables so we can have a look at the structure but he avoided that.

At least we would be able to see whats going on.

[wcrimi]

First, let me say I am extremely appreciative of all the help I've been getting. Please do not think I am ignoring anything.

Here's what I've done so far.

1. I took the 12/15 backup (which was theoretically fine), brought it back up to date (which was 12/22) and then checked if everything was OK.

That failed. I continued to get errors on the Compact and Repair.

That suggested to me that it was something other than a corrupt database. Some of the original error messages suggested that there were "resource issues" that wound up seemingly randomly deleting some tables and queries.

2. I took the 12/15 backup (which was theoretically fine), copied it, and deleted everything other than two very large tables.
2a. I took the 12/15 backup (which was theoretically fine), deleted those two very large tables, but then linked them back in from the new database in step 2.

So at this point I had split the original 12/15 backup into 2 databases that were functionally the same as the original, but that should use way less resources during a Compact Repair.

2b. I then brought everything up to date as of 12/22.


That failed. I continued to get errors on the Compact and Repair on each. Sometimes the errors were related to keys (and it undid the key setting for the table) and sometimes it randomly deleted the error table.


Now the new news.


3. I noticed that somehow (and I have no explanation for this) I had several hundred duplicate records in one of my tables among the new data records (between 12/16 and 12/22). Somehow the data for 1 table was imported 3 times. Believe me, this is literally almost impossible because multiple tables are updated using a single macro and only this one table had dups.

4. I manually deleted all the duplicates.

I didn't have an issue.

Praise God!!!!!

5. I just did all my normal Monday processing and it all worked. I now have a split database that's up to date to 12/29 that is not getting errors.

I didn't have an issue.

Praise God!!!!!

6. I just backed all that up.

7. I have no idea how this all happened or if this is permanently fixed. I think I was clearly having some kind of resource issue at the beginning. However, I was also getting key errors. I also have no idea how the duplicate records came to be or if they were somehow the issue.

But at least now I have something that is complete and functioning with a good backup as 12/29. If by some chance it blows up again (which is probably 50-50 and would suggest there is other corruption), I will proceed to create a brand new database and do the imports as some people have suggested.

[CJ_London]

Personally I would do the brand new database now. All tables closely related to each other should be in one db and not split as you have based on size.