We are using SQL Anywhere 12.0.1 as an embedded database in our application, which tracks information about vehicles that are observed by our company's proprietary license plate readers. As such, we install the minimum sized SA build we can on client machines. The only query tool that is installed is dbisqlc.exe.
When the application is installed for the first time, the database is initially 2 GB in size. For many installations, it never gets much bigger than this. But there are locations where it does grow.
There is one install of our software where that has anywhere from 15,000 to 18,000 vehicles that drive by it in both directions. We have a process that runs at midnight every day to purge data than a certain age which have replicated to the server. So the database has up to 11 days worth of data in it, and at midnight, the 11th day is purged. That means that it can have almost 200,000 rows in that one table at any time, not to mention another 400,000 in a related table & an average of 400,000 in a third.
This install replicates using a cell modem, and it turns out that the connectivity there is spotty. Plus it was recently upgraded and it took longer than the period the installer gives the database server to shut down for it to stop, so the upgrade failed. Upon remotely connecting into the location, we found that the database is currently 25 GB in size.
Given the history of spotty cell coverage at the site, my current hypothesis is there was a period in the past when the cell modem was unable to connect to our server for an extended period. If data doesn't replicate, it doesn't get purged, so this could cause the tables in question to grow beyond normal bounds. Plus the server isn't stopped unless there's an upgrade underway, so there's no opportunity for it to shrink.
To be honest, I don't even know if 12.0.1 will ever free unused database pages and shrink the file.
So I need to gather some statistics about each table, how many pages it has in the table itself & all indexes, add up all those numbers, multiply by the page size, and output a number telling me how much space is being used, in total, how much is free, and do it with dbisqlc. Computing other meaningful statistics would be cool, too, but as I'm a developer and not a DBA, I have no idea what those might be.
If someone could point me in the right direction, or just give me the queries, I'd appreciate it.
asked 22 Aug '14, 14:00
You are correct that the unused free space will not be reclaimed. if you would like this space back, you can unload and reload the database using the dbunload utility to reclaim the space.
The dbinfo utility might provide the functionality you require. If you would like to do this in SQL, you can check the number of set bits is the allocation bit maps (in SYSTAB and SYSPHYSIDX). Queries below are untested, but should do the job.
answered 22 Aug '14, 14:49
It turns out that there's a query in a module in my code that computes space usage for purging data that occupies more than x% of the table. I was able to use the following query:
From this I was able to show that the database in question is 96% free.
answered 22 Aug '14, 15:12