I was performing the diagnostic tools on a 50K RM database file recently. I did them in order and everything was “ok” except when I compacted the DB. I got the following error:
“SQLite Error 1 - cannot VACUUM - SQL statements in progress.”
When I close this error window, the window that says “Compacting database… This can take a long tome on very large databases.” is present but I don’t think it is working as after an hour it isn’t done compacting.
Any help is appreciated.
It’s been experienced before. RM has a propensity to fail to complete a transaction in some process, sometimes. A general guideline is to run the database tools immediately on opening the database, not after working on it. That can help avoid the situation and is just a workaround of a program fault.
Another is that you rarely need to compact a database so don’t make it a religious observance.
When you get this error, you will have to close the application and restart it to get compact to run. You may have to force-close. No sense waiting if RM is frozen.
Thank you Tom for this info. Unfortunately, I have closed the app and restarted it and ran the compact DB again but, alas, the error persists!
Then that indicates that a roll-back journal file associated with the database still exists. Normally, the SQLite database engine deletes the file on successful completion of the transaction that has modified the database. While we don’t know why the transaction did not complete or if the database is perfectly alright, you will have to delete the journal file so that the database can be worked on. Look for it in the same folder as the database file with the same name appended with “journal”.
Also,preserve your most recent backup prior to this failure if you find that your database is screwed up so that you can restore the prior version.
I hate to give trite advice, but this may be one of those rare cases where you will need to reboot your computer before restarting RM7 and running the database tools as the first thing after starting RM7. There has to be some process somewhere which is trying to access your RM7 database.
Also, is your database stored in a Dropbox folder or a OneDrive folder or a Google Drive folder or anything like that? If so, you should pause the sync between folder and the cloud while running RM7.
The RM9 compact tool does not tell you what impact if any it had on file size. FTM shows the percent reduction and users are supposed to repeat until it is zero. Making a few data changes can easily result in a 1 or 2 percent file reduction so I run this often.
Jerry: restart is so often exactly what is needed to kill “magic gremlins” (your computer is laughing at you).
Having used FTM – I think the % reduction compacting was misleading and meaningless. FTM used to advise to compact multiple times til you reached Zero – I usually compact only when I have made significant changes (not additions) to the (RM) db. Or if I can’t remember when last time was.
You confuse the discussion by bringing FTM into the picture. Its “compact” is different from RM’s so the operating instructions are also different. RM’s “compact” only calls the SQLite “vacuum” tool for which one run is good enough if needed at all. Under FTM’s “compact”, there is not only SQLite vacuum but other programmed data checks and operations such as RM’s “Delete Phantoms”. Because of the many tables and dependencies among them, I suspect these additional procedures have to be reiterated to complete their job. Vacuum returns disk space previously occupied by now deleted records. Reduction of file size by successive compacts is just a proxy to indicate when such cleanup is complete. In RM, there is no reason to reiterate compact - once is enough.
As one further tidbit to Tom’s excellent explanation, an SQLite vacuum operation is actually a copy operation. The SQLite vacuuming does not happen in place. A new copy of the database is made in such a way that empty rows are not copied. Therefore, the new copy of the database has no empty rows and another vacuum operation would have no empty rows to omit. That’s why successive SQLite vacuum operations would have no further effect. After the copy is successful, the original database rows effectively are deleted and the new copy effectively becomes the database.
The copy operation that is performed by the SQLite vacuum operation is completely transparent not only to the RM user but also to a user of SQLite such as RM. RM doesn’t see a copy or a new database or a delete. RM doesn’t say to SQLite to “copy the database and omit the empty rows while you are copying”. Instead, RM simply says to SQLite to “vacuum the database” and SQLite does the rest. For all RM knows, the vacuum actually is happening in place. We know that the vacuum is not happening in place, but it doesn’t really matter whether it’s happening in place or not. The final result is the same either way.
It still would be useful to get an indication of file “size” reduction or other parameter to know how often to use this tool. Many of these file tools combined into one menu choice and executed in the appropriate sequence would simplify things too…
What is “compact” and where is it in the menu?
The Compact tool may be found in the menu under Files => Tools => Compact
The Compact tool removes unused space from your database. The unused space arises after delete operations. The space for deleted people or deleted facts or deleted sources or deleted places or deleted anything becomes unused. Also, there are many places in RM where you can merge two items such as merging people or merging places. The merge operation results in deleting of one of the two merged items while keeping the other one.
You can’t see the unused space nor can you tell how much unused space there is. The only way to tell is to run the Compact tool and to compare the size of your RM database from before and after the Compact. You will need to look at the size of your database from Windows File Explorer or from Finder on a Mac. There is nothing in RM itself that tells you how much disk space is being used by your database.
For the most part, such unused space is a very minor issue and is not worth worrying about. But after many months or sometimes many years of use of the same database, or after many delete or merge operations, the unused space can build up. For most RM users, I don’t think it’s necessary to use the Compact tool every day or every week or even every month. But it probably should be used at least a couple of times per year. And it probably should be used after lots of deleting or lots of merging.
Thank you. I will start using it.