'System.Data.SQLite Close() not releasing database file

I'm having a problem closing my database before an attempt to delete the file. The code is just

 myconnection.Close();    
 File.Delete(filename);

And the Delete throws an exception that the file is still in use. I've re-tried the Delete() in the debugger after a few minutes, so it's not a timing issue.

I have transaction code but it doesn't run at all before the Close() call. So I'm fairly sure it's not an open transaction. The sql commands between open and close are just selects.

ProcMon shows my program and my antivirus looking at the database file. It does not show my program releasing the db file after the close().

Visual Studio 2010, C#, System.Data.SQLite version 1.0.77.0, Win7

I saw a two year old bug just like this but the changelog says it's fixed.

Is there anything else I can check? Is there a way to get a list of any open commands or transactions?

New, working code:

 db.Close();
 GC.Collect();   // yes, really release the db

 bool worked = false;
 int tries = 1;
 while ((tries < 4) && (!worked))
 {
    try
    {
       Thread.Sleep(tries * 100);
       File.Delete(filename);
       worked = true;
    }
    catch (IOException e)   // delete only throws this on locking
    {
       tries++;
    }
 }
 if (!worked)
    throw new IOException("Unable to close file" + filename);

sqlite system.data.sqlite

Solution 1:^[1]

Just GC.Collect() didn't work for me.

I had to add GC.WaitForPendingFinalizers() after GC.Collect() in order to proceed with the file deletion.

Solution 2:^[2]

Had a similar issue, though the garbage collector solution didn't fix it.

Found disposing of SQLiteCommand and SQLiteDataReader objects after use saved me using the garbage collector at all.

SQLiteCommand command = new SQLiteCommand(sql, db);
command.ExecuteNonQuery();
command.Dispose();

Solution 3:^[3]

In my case I was creating SQLiteCommand objects without explicitly disposing them.

var command = connection.CreateCommand();
command.CommandText = commandText;
value = command.ExecuteScalar();

I wrapped my command in a using statement and it fixed my issue.

static public class SqliteExtensions
{
    public static object ExecuteScalar(this SQLiteConnection connection, string commandText)
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = commandText;
            return command.ExecuteScalar();
        }
    }
}

The using statement ensures that Dispose is called even if an exception occurs.

Then it's a lot easier to execute commands as well.

value = connection.ExecuteScalar(commandText)
// Command object created and disposed

Solution 4:^[4]

The following worked for me:

MySQLiteConnection.Close();
SQLite.SQLiteConnection.ClearAllPools()

More info: Connections are pooled by SQLite in order to improve performance.It means when you call Close method on a connection object, connection to database may still be alive (in the background) so that next Open method become faster.When you known that you don't want a new connection anymore, calling ClearAllPools closes all the connections which are alive in the background and file handle(s?) to the db file get released.Then db file may get removed, deleted or used by another process.

Solution 5:^[5]

I was having a similar problem, I've tried the solution with GC.Collect but, as noted, it can take a long time before the file becomes not locked.

I've found an alternative solution that involves the disposal of the underlying SQLiteCommands in the TableAdapters, see this answer for additional information.

Solution 6:^[6]

I've been having the same problem with EF and System.Data.Sqlite.

For me I found SQLiteConnection.ClearAllPools() and GC.Collect() would reduce how often the file locking would happen but it would still occasionally happen (Around 1% of the time).

I've been investigating and it seems to be that some SQLiteCommands that EF creates aren't disposed and still have their Connection property set to the closed connection. I tried disposing these but Entity Framework would then throw an exception during the next DbContext read - it seems EF sometimes still uses them after connection closed.

My solution was to ensure the Connection property is set to Null when the connection closes on these SQLiteCommands. This seems to be enough to release the file lock. I've been testing the below code and not seen any file lock issues after a few thousand tests:

public static class ClearSQLiteCommandConnectionHelper
{
    private static readonly List<SQLiteCommand> OpenCommands = new List<SQLiteCommand>();

    public static void Initialise()
    {
        SQLiteConnection.Changed += SqLiteConnectionOnChanged;
    }

    private static void SqLiteConnectionOnChanged(object sender, ConnectionEventArgs connectionEventArgs)
    {
        if (connectionEventArgs.EventType == SQLiteConnectionEventType.NewCommand && connectionEventArgs.Command is SQLiteCommand)
        {
            OpenCommands.Add((SQLiteCommand)connectionEventArgs.Command);
        }
        else if (connectionEventArgs.EventType == SQLiteConnectionEventType.DisposingCommand && connectionEventArgs.Command is SQLiteCommand)
        {
            OpenCommands.Remove((SQLiteCommand)connectionEventArgs.Command);
        }

        if (connectionEventArgs.EventType == SQLiteConnectionEventType.Closed)
        {
            var commands = OpenCommands.ToList();
            foreach (var cmd in commands)
            {
                if (cmd.Connection == null)
                {
                    OpenCommands.Remove(cmd);
                }
                else if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection = null;
                    OpenCommands.Remove(cmd);
                }
            }
        }
    }
}

To use just call ClearSQLiteCommandConnectionHelper.Initialise(); at the start of application load. This will then keep a list of active commands and will set their Connection to Null when they point to a connection that is closed.

Solution 7:^[7]

Try this... this one tries all the above codes... worked for me

    Reader.Close()
    connection.Close()
    GC.Collect()
    GC.WaitForPendingFinalizers()
    command.Dispose()
    SQLite.SQLiteConnection.ClearAllPools()

Hope that helps

Solution 8:^[8]

Use GC.WaitForPendingFinalizers()

Example:

Con.Close();  
GC.Collect();`
GC.WaitForPendingFinalizers();
File.Delete(Environment.CurrentDirectory + "\\DATABASENAME.DB");

Solution 9:^[9]

I believe the call to SQLite.SQLiteConnection.ClearAllPools() is the cleanest solution. As far as I know it is not proper to manually call GC.Collect() in the WPF environment. Although, I did not notice the problem until I have upgraded to System.Data.SQLite 1.0.99.0 in 3/2016

Solution 10:^[10]

Had a similar problem. Calling Garbage Collector didn't help me. LAter I found a way to solve the problem

Author also wrote that he did SELECT queries to that database before trying to delete it. I have the same situation.

I have the following code:

SQLiteConnection bc;
string sql;
var cmd = new SQLiteCommand(sql, bc);
SQLiteDataReader reader = cmd.ExecuteReader();
reader.Read();
reader.Close(); // when I added that string, the problem became solved.

Also, I don't need to close database connection and to call Garbage Collector. All I had to do is to close reader which was created while executing SELECT query

Solution 11:^[11]

I was struggling with the similar problem. Shame on me... I finally realized that Reader was not closed. For some reason I was thinking that the Reader will be closed when corresponding connection is closed. Obviously, GC.Collect() didn't work for me.
Wrapping the Reader with "using: statement is also a good idea. Here is a quick test code.

static void Main(string[] args)
{
    try
    {
        var dbPath = "myTestDb.db";
        ExecuteTestCommand(dbPath);
        File.Delete(dbPath);
        Console.WriteLine("DB removed");
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
    Console.Read();
}

private static void ExecuteTestCommand(string dbPath)
{
    using (var connection = new SQLiteConnection("Data Source=" + dbPath + ";"))
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "PRAGMA integrity_check";
            connection.Open();
            var reader = command.ExecuteReader();
            if (reader.Read())
                Console.WriteLine(reader.GetString(0));

            //without next line database file will remain locked
            reader.Close();
        }
    }   
}

Solution 12:^[12]

Maybe you don't need to deal with GC at all. Please, check if all sqlite3_prepare is finalized.

For each sqlite3_prepare, you need a correspondent sqlite3_finalize.

If you don't finalize correctly, sqlite3_close will not close the connection.

Solution 13:^[13]

Best answer that worked for me.

dbConnection.Close();
System.Data.SQLite.SQLiteConnection.ClearAllPools();

GC.Collect();
GC.WaitForPendingFinalizers();

File.Delete(Environment.CurrentDirectory + "\\DATABASENAME.DB");

Solution 14:^[14]

This works for me but i noticed sometimes journal files -wal -shm are not deleted when the process is closed. If you want SQLite to remove -wal -shm files when all connection are close the last connection closed MUST BE non-readonly. Hope this will help someone.

Solution 15:^[15]

The reason for this seems to be a feature called "Pooling". Appending "Pooling=false" to the connection string causes the DB-File to be released with "connection.Close()".

See the FAQ on connection pooling here: https://www.devart.com/dotconnect/sqlite/docs/FAQ.html#q54

Solution 16:^[16]

I was using SQLite 1.0.101.0 with EF6 and having trouble with the file being locked after all connections and entities disposed.

This got worse with updates from the EF keeping the database locked after they had completed. GC.Collect() was the only workaround that helped and I was beginning to despair.

In desperation, I tried Oliver Wickenden's ClearSQLiteCommandConnectionHelper (see his answer of 8 July). Fantastic. All locking problems gone! Thanks Oliver.

Solution 17:^[17]

Waiting for Garbage Collector may not release the database all time and that happened to me. When some type of Exception occurs in SQLite database for example trying to insert a row with existing value for PrimaryKey it will hold the database file until you dispose it. Following code catches SQLite exception and cancels problematic command.

SQLiteCommand insertCommand = connection.CreateCommand();
try {
    // some insert parameters
    insertCommand.ExecuteNonQuery();
} catch (SQLiteException exception) {
    insertCommand.Cancel();
    insertCommand.Dispose();
}

If you not handle problematic commands' exceptions than Garbage Collector cannot do anything about them because there are some unhandled exceptions about these commands so they are not garbage. This handling method worked well for me with waiting for garbage collector.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	djikay
Solution 2	Irshad
Solution 3
Solution 4	Lorenzo Polidori
Solution 5	Community
Solution 6	Irshad
Solution 7	Bishnu Dev
Solution 8	Graham
Solution 9	Peter O.
Solution 10
Solution 11	Mike Znaet
Solution 12	João Monteiro
Solution 13	kazem fallahikhah
Solution 14
Solution 15	Dokug
Solution 16	Tony Sullivan
Solution 17	Muhammed Kadir

'System.Data.SQLite Close() not releasing database file

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]

Solution 15:[15]

Solution 16:[16]

Solution 17:[17]