Re: [sqlite] VACUUM silently fails, database size not changed
Unfortunately this does not help :-( It may have still something to do with WAL. Before I run the VACUUM the WAL file is quite small. After the VACUUM has completed, it is about 20 MB - about the same size as the properly compacted database would be. But when I run a pragma wal_checkpoint; the WAL file does not shrink and neither the database. When I close the database, the WAL is deleted but the database remains at 120 MB. Running a vacuum with the sqlite3 command line utility afterwards shrinks the database to 20 MB. There must be some kind of condition or lock or whatever in my app which prevents the vacuum to work properly. It returns success and the WAL file grows, but the database size is not reduced. I must be missing a step ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM silently fails, database size not changed
On 08/31/2013 08:29 PM, Mario M. Westphal wrote:
I run the VACUUM command at the end of a diagnosis and cleanup operation on
my database.
I use the SQLite 3 API on Windows 7. Latest version of SQLite.
My database uses the WAL mode.
The database size is 120 MB when I run the sqlite3_execute("VACUUM",...)
command.
After about 20 seconds of heavy disk activity, sqlite3_execute returns with
SQLITE_OK.
The databate size on disk remains unchanged although I can see the sizeof
the WAL file rise to about 20 MB.
I had expected that the database shrinks because a lot of data has been
removed.
I closed my application and used the command line sqlite3.exe to VACUUM it.
The database shrinks from 120 MB to 20 MB!
Question:
I checked for open transactions: None.
I checked for pending statements (with sqlite3_next_stmt()): None.
Are there other reasons why VACUUM runs (it takes maybe 20 seconds for it to
return, and there is high disk activity during the execute) successfully but
apparently does nothing?
Perhaps you have auto-checkpointing turned off, or set to a very large
value:
http://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint
If so, the changes related to VACUUM are still in the WAL file. To
flush them through to (and truncate) the db file, run an
explicit checkpoint using "PRAGMA wal_checkpoint" after the VACUUM.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VACUUM silently fails, database size not changed
I run the VACUUM command at the end of a diagnosis and cleanup operation on
my database.
I use the SQLite 3 API on Windows 7. Latest version of SQLite.
My database uses the WAL mode.
The database size is 120 MB when I run the sqlite3_execute("VACUUM",...)
command.
After about 20 seconds of heavy disk activity, sqlite3_execute returns with
SQLITE_OK.
The databate size on disk remains unchanged although I can see the sizeof
the WAL file rise to about 20 MB.
I had expected that the database shrinks because a lot of data has been
removed.
I closed my application and used the command line sqlite3.exe to VACUUM it.
The database shrinks from 120 MB to 20 MB!
Question:
I checked for open transactions: None.
I checked for pending statements (with sqlite3_next_stmt()): None.
Are there other reasons why VACUUM runs (it takes maybe 20 seconds for it to
return, and there is high disk activity during the execute) successfully but
apparently does nothing?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
