Product Version: 5.6
Expires on: 365 days from publish date
Updated: Aug 30, 2013
Hibernate.TransactionException: Begin failed with SQL exception ---> System.Data.SQLite.SQLiteException: The database file is locked
Getting error after 5.6 upgrade. This can happen when the Inventory module is turned on and recording a lot of data, thus making the db file inordinately large. In 5.6 a purging method was added to alleviate this.
*** As a precaution before doing below backup .db first***
1. User can open their database in their favorite SQLite Browser. One preference is SQLiteSpy (http://download.cnet.com/SQLiteSpy/3000-2065_4-75451503.html).
If you want to remove a specific date and beyond copy the following SQL commands in and execute them (this one deletes anything older than 10 days):
DELETE FROM CollectorSystem WHERE DateCreated < DATETIME('now', '-10 day');
DELETE FROM CollectorRedirection WHERE CollectorRedirection.Id IN ( SELECT CR.Id FROM CollectorRedirection CR LEFT JOIN CollectorSystem CS ON CR.SystemCollectorId = CS.Id WHERE CS.Id is NULL );
DELETE FROM CollectorFlexAppUia WHERE CollectorFlexAppUia.Id IN ( SELECT CFAU.Id FROM CollectorFlexAppUia CFAU LEFT JOIN CollectorSystem CS ON CFAU.SystemCollectorId = CS.Id WHERE CS.Id is NULL );
DELETE FROM CollectorApplication WHERE CollectorApplication.Id IN ( SELECT CA.Id FROM CollectorApplication CA LEFT JOIN CollectorSystem CS ON CA.SystemCollectorId = CS.Id WHERE CS.Id is NULL );
DELETE FROM CollectorThinApp WHERE CollectorThinApp.Id IN ( SELECT CTA.Id FROM CollectorThinApp CTA LEFT JOIN CollectorSystem CS ON CTA.SystemCollectorId = CS.Id WHERE CS.Id is NULL );
This command can take several minutes to perform since those tables are huge.
If you wish to remove all the collector records instead, do the following (if they don't need the records, this is recommended since it's faster):
DELETE FROM CollectorSystem;
DELETE FROM CollectorRedirection;
DELETE FROM CollectorFlexAppUia;
DELETE FROM CollectorApplication;
DELETE FROM CollectorThinApp ;
This one should run much faster as it's a blind delete.
The other thing that should be done is to vacuum the database after the files are purged. It should shrink the filesize down.