System.Data.SQLite

I am trying to allow a user to interrupt a long running vacuum on a large (2Gb) database.

The vacuum command is executing in the background but I can use the journal file size to report approximate progress to the UI thread.

Initially the vacuum can be interrupted successfully during the 'analysing' phase before the journal file is created. However once the journal file is created and defragmenting/re-indexing is proceeding the vacuum cannot be interrupted. The progress shows the entire journal file being populated before the interrupt finally has an effect and cancels the vacuum. To the user it looks like the vacuum has not been interrupted and continues.

As the user has decided to cancel the vacuum hopefully all that is required is to delete the journal and return to using the original database file.

This would allow a long running vacuum to be interrupted immediately.

I am using SQLiteConnection.Cancel() (followed by SQLiteConnection.Close()) on another thread to the vacuum but using same connection the vacuum is using (this seems to be necessary though according to the documentation it is not safe). It does work and as I've said already it will cancel the vacuum immediately before the journal file is created but not until the end once the journal file is created. Here is the code that implements the cancel:-

        public void CancelCurrentDbCommand()
        {
            _syncContextCancel = SynchronizationContext.Current;

            ThreadPool.QueueUserWorkItem(CancelWork, null);
        }

        private void CancelWork(object state)
        {
            bool success = false;

            try
            {
                _connection.Cancel();
                _connection.Close();
                success = true;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message, ex);
            }

            SyncContextCancel.Send(CancelCompleted, new object[] { success });
        }

        public void CancelCompleted(object state)
        {
            object[] args = (object[])state;
            bool success = (bool)args[0];

            if (success)
            {
                log.Debug("long running query cancelled" + DateTime.Now.ToLongTimeString());

            }
        }

The log file shows the cancel was successful several minutes before the vacuum completes.

Please can you interrupt the defrag/re-index, dump the journal and return to the original database once interrupt (SQLiteConnection.Cancel) is received.

Closing the database from another thread is not allowed.