Shrink the SQLite event and trends databases (Option 2) by creating a new file

[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 database can grow really big, although not all the space in the files is used. In extreme cases, there is more free space than used space and it is quicker to create a separate file.

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

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. VACUUM main INTO 'C:\ProgramData\Schneider Electric EcoStruxure\Building Operation 6.0\Enterprise Server\db\Historical\event2.db';
7. Update the path to match your installation. One can SHIFT-RIGHT-CLICK the event.db or trend.db and select Copy as Path, then select everything inside the single quotes and paste. The paste will include double quotes, so delete those and leave the single quotes
8. Update the database name to ensure it is unique.
9. Click the blue play button above the Query tab.
10. Depending on the size and amount of free space, this can take seconds to days to complete. One can monitor the progress in Windows Explorer by seeing the Size and Date Modified field update.
11. Append .old to the end of the original file and rename the new file, event.db or trend.db, so it replaces the original.
12. Start the Enterprise Server or Enterprise Central.