Verify the need to shrink the SQLite event and trends databases

[David_Purser]

Warning

Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support for assistance.

Issue

In some cases, the event and trend databases can grow really big, although not all the space in the files is used. Therefore, one needs a way to verify that vacuuming the database will help.

Product Line

EcoStruxure Building Operation

Environment

• SQLite
• Building Operation Enterprise Server
• Building Operation Enterprise Central

Cause

Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file. Running VACUUM ensures that each table and index is largely stored contiguously within the database file. In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further.

Read further information here: https://www.sqlite.org/lang_vacuum.html

Definitions are documented here: https://www.sqlite.org/pragma.html 

PRAGMA schema.freelist_count; Returns the number of unused pages in the database file.

PRAGMA schema.page_count; Returns the total number of pages in the database file.

PRAGMA schema.page_size; Returns the page size in bytes (B), which must be a power of two between 512 and 65536.

Resolution

1. Stop the Enterprise Server or Enterprise Central.
2. Open SQLiteStudio (link to download)
3. Select "Databases" --> "Add database"
4. Find the event.db or trend.db file from the "historical" folder of the EBO database folder. Use this article to locate for your installation - Clean db for Enterprise Server or Enterprise Central
5. On the added database, right-click and select Connect to the database
6. In the query tab, paste the following command:

   1. SELECT
      	(SELECT freelist_count FROM pragma_freelist_count()) AS FreeList_Count,
          (SELECT page_count FROM pragma_page_count()) AS Page_Count,
          ((SELECT page_count FROM pragma_page_count()) - (SELECT freelist_count FROM pragma_freelist_count())) AS Used_Page_Count,
          ROUND(CAST((CAST((SELECT freelist_count FROM pragma_freelist_count()) AS REAL) / CAST((SELECT page_count FROM pragma_page_count()) AS REAL)) * 100 AS NUMERIC),2) AS Percent_Free,
      	(SELECT page_size FROM pragma_page_size()) AS Page_Size_B,
          ROUND(CAST(((SELECT freelist_count FROM pragma_freelist_count()) * (SELECT page_size FROM pragma_page_size())) / (1024.0 * 1024.0) AS NUMERIC),3) AS Estimated_Space_MB;
      

7. Click the blue play button above the Query tab. 
8. Review the Percent_Free and Estimated_Space_MB, which will be returned at a minimum, to decide if you want to perform the 'vacuum' command. The vacuum process is slow, as it was designed to operate in the background. Shrink the SQLite event and trend databases. If the Percent_Free is above 80, this is the better option: Shrink the SQLite event and trends databases (Option 2) by creating a new file.