3

In SQLite the size of the database file is the overall size of the database, but this is not necessarily a correct indication of the actual space used. If data is deleted, the freed space is not returned to the operating system. One has to execute the VACUUM command to defragment the database and release unused space back to the operating system.

I am using the Devart ADO.Net data provider for SQLite.

Is there a way to obtain the actual used space of a SQLite database?

Elan
  • 6,084
  • 12
  • 64
  • 84

2 Answers2

6

I found a pragma freelist_count, which according to the SQLite documentation provides the number of unused pages in the database file. This is not precise as far as the total free space within the database file, as there can still be many pages that are partially filled.

Nonetheless, the below still provides us with an accurate count of free pages and suffices for my needs in getting a rough estimate of the used versus free space within the database file.

PRAGMA freelist_count;
PRAGMA page_size;
Elan
  • 6,084
  • 12
  • 64
  • 84
  • You can thus get the amount of used space by doing `SELECT (page_count - freelist_count) * page_size as size FROM pragma_page_count(), pragma_freelist_count(), pragma_page_size();` – phil_rawlings Jun 18 '22 at 15:11
0

Multiplying the values returned by the page_count and page_size pragmas might give you a good estimate of the size of the DB:

PRAGMA page_count;
PRAGMA page_size;
Paul Lefebvre
  • 6,253
  • 3
  • 28
  • 36
  • I tried it, but this still gave me the same size as the file itself. According to documentation it states for page_count: "Return the total number of pages in the database file.". See: http://www.sqlite.org/pragma.html#pragma_page_count – Elan May 07 '11 at 04:08