How can we help you today?
VistaDB file size is significantly larger than SQL CE file size
1 Comment
Kendall Miller
said
almost 4 years ago
Thanks for reaching out to us!
In most cases, VistaDB does "soft deletes" which is part of how it allows greater concurrency for multiple writers at the same time. The pack command rewrites the file to get rid of orphaned rows. We are looking at options for a future version of VistaDB where it can "scavenge" those rows (like SQL Server does) without affecting concurrency but don't have a fixed point for that on the roadmap.
There are two things I would recommend:
First, you want to think through a moment in application usage that is good for database maintenance & a backup. What we generally recommend is that there is some business moment or frequency where you can prompt the user to let you make a backup of the database (backups are good!) and take that moment to do both a backup and a pack.
Second, you can somewhat control how fast the file grows by selecting a page size that is sufficient for your needs but no larger - larger pages mean more disk space is used for just about anything in the database. But, you can't shrink the page size too small or you can't have as many columns as you need in your schema and performance can suffer if there are two few rows in a page.
Nilanka Dharmadasa
We are considering the option of replacing SQL CE with VistaDB for the local database of our application. But we noticed that with VistaDB the file size keeps on growing and it gets significantly larger than SQL CE file size. Example : VistaDB goes beyond 15MB while SQL CE sits around 1-2 MB.
If Pack Database is performed, then it reduces the size back to 1-2 MB range (Still larger than SQL CE). But that's not an option as multiple instances of the application can access the VistaDB file, hence it's not always possible to get exclusive access to the file.
Is there any other option or any optimisation strategies to reduce the file size?