VB.Net/C# Extension Method to Shrink SQLite Database

Posted by Blake on 11/6/2014
)

The vacuum command in SQLite provides a way to effectively shrink the database. It does this by copying the contents of the db into a temporary database, rebuilding the main database and then copying the data back in. This can help fragmented databases where a table or indexes maybe stored in different sections of the file (the database can become fragmented over time with many inserts/updates/deletes). After a vacuum the tables/indexes will largely be stored contiguously.

I have a simple extension method off of SQLiteConnection that I use when I want to shrink the database from code. Usage just requires that the connection is opened when "CompactDatabase" is called.

VB.Net

    ''' <summary>
    ''' Compacts (vacuum) or shrinks a SQLite database via the open SQLiteConnection.  If an open transaction exists or there are open
    ''' queries this command will fail.
    ''' </summary>
    ''' <param name="conn">An open SQLite connection that has no pending transactions open or queries running.</param>
    ''' <remarks></remarks>
    <Extension()> _
    Public Sub CompactDatabase(conn As SQLiteConnection)
        Using cmd As System.Data.SQLite.SQLiteCommand = conn.CreateCommand
            cmd.CommandText = "vacuum"
            cmd.ExecuteNonQuery()
        End Using
    End Sub

C#

    /// <summary>
    /// Compacts (vacuum) or shrinks a SQLite database via the open SQLiteConnection.  If an open transaction exists or there are open
    /// queries this command will fail.
    /// </summary>
    /// <param name="conn">An open SQLite connection that has no pending transactions open or queries running.</param>
    /// <remarks></remarks>
    [Extension()]
    public void CompactDatabase(SQLiteConnection conn)
    {
	    using (System.Data.SQLite.SQLiteCommand cmd = conn.CreateCommand) {
		    cmd.CommandText = "vacuum";
		    cmd.ExecuteNonQuery();
	    }
    }