Compact DB on startup

[johnselkirk]

Hi,

I am using EF with a SQL Lite DB which has 'Journal Mode=WAL' set in the connection string as there are a few processes that could be accessing the DB.

I would like to compact the DB on startup of any of processes and have no clue if that is safe to do or what the best setting is to do that. (connection string/code/??)

Any advice would be greatly appreciated.

John.

[Shalex]

Please execute the "VACUUM" statement to compact your SQLite database:

Code: Select all

using (SQLiteCommand cmd = conn.CreateCommand())
{
  cmd.CommandText = "VACUUM";
  cmd.ExecuteNonQuery();
}

[Fretek]

Please execute the "VACUUM" statement to compact your SQLite database:

Code: Select all

using (SQLiteCommand cmd = conn.CreateCommand())
{
  cmd.CommandText = "VACUUM";
  cmd.ExecuteNonQuery();
}

Which way is best to use? Code (as in the example above) or with connctionsting - AutoVaccuum = Full?
What is the difference between these two ways?

[Fretek]

Please execute the "VACUUM" statement to compact your SQLite database:

Code: Select all

using (SQLiteCommand cmd = conn.CreateCommand())
{
  cmd.CommandText = "VACUUM";
  cmd.ExecuteNonQuery();
}

Which way is best to use? Code (as in the example above) or with connctionsting - AutoVaccuum = Full?
What is the difference between these two ways?

Wish to know which method is considered the best and fastest

[Shalex]

Sorry for the delay.

The "AutoVaccuum=Full;" connection string parameter turns on the following pragma: http://sqlite.org/pragma.html#pragma_auto_vacuum.

The SQLiteCommand with the "VACUUM" command text calls this command: http://sqlite.org/lang_vacuum.html.