SQLite Forum

intterupted vacuum

intterupted vacuum

(1) By Roman (moskvich412) on 2022-01-12 00:01:31 [link] [source]

Dear SQLiters

Before vacuuming a 100GB database, I failed to realize that as much as twice free space is needed. I prepared a factor 150GB. Vacuum failed with "disk full" error. I created more free disk space and wanted to resume vacuum. Both times (first and after the error) I wanted to allow SQLite to use 3G of RAM to speed up using PRAGMA cache_size = -3000000. It was using 3GB (actually 6GB, as it should) during the first (failed) attempt. But as soon as I executed

PRAGMA cache_size = -3000000;

it seems to have resumed vacuuming, but without using RAM. This means it will be very slow. Is there a way to get it to use RAM? (I am using version 3.33)

Thank you,


(2) By Richard Hipp (drh) on 2022-01-12 00:13:26 in reply to 1 [link] [source]

Have you tried using VACUUM INTO to create a vacuumed copy of the original database? When the copy has been created, then just delete the original and rename the copy back into the original name. (NB: This will only work if the database is not being used simultaneously by some other process.)

(3) By Roman (moskvich412) on 2022-01-12 00:17:25 in reply to 2 [link] [source]

Dear Richard,

As soon as I execute

PRAGMA cache_size = -3000000;

sqlite3 shell starts doing something (command did not return for one hour so far). I presume it resumed vacuum.

Should I interrupt it and try VACUUM INTO? I do not see how this will get it to use more RAM.


(4) By Simon Slavin (slavin) on 2022-01-12 10:51:25 in reply to 3 [source]

My guess is that it's not resuming the VACUUM, it's trying to check/rescue data from your corrupt database using both the database file and any journal files. Since a lot of new data was written, and it all has to be read, the rescue takes a lot of time.

Make sure you have at least twice as much free space on the volume as the database file takes up. Use any SQLite program to open the corrupted database and let the rescue procedure take as long as it takes. Then allow the program to quit normally. You would not lose any data if this is allowed to proceed.

Do not use any more VACUUM commands until opening the database takes the normal amount of time, and you have a lot of free space on that volume.

(5) By Roman (moskvich412) on 2022-03-04 19:51:28 in reply to 4 [link] [source]

Thank you, Simon. I forgot to reply earlier. That is exactly what I did and all is OK. SQLite is very robust!