on 2014 Aug 22 3:00 PM
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.
Request clarification before answering.
Hi Tony,
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.
SELECT T.table_name, I.index_name, (DB_PROPERTY( 'PageSize' )*count_set_bits(p.allocation_bitmap))/1024 as "Usage (KB)"
FROM sys.sysidx AS i
JOIN sys.systab AS t
ON T.table_id = I.table_id
JOIN sys.sysphysidx AS p
ON p.table_id = i.table_id
AND p.phys_index_id = i.phys_index_id
ORDER BY "Usage (KB)" DESC;
SELECT T.table_name,
ISNULL((DB_PROPERTY( 'PageSize' )*count_set_bits(tab_page_list))/1024,0) as "Table Page Usage (KB)",
ISNULL((DB_PROPERTY( 'PageSize' )*count_set_bits(ext_page_list))/1024,0) as "Ext Page Usage (KB)"
FROM SYSTAB t;
If you are interested in monitoring these items, you can create scheduled events that check these values. Such an event could email a notifcation to remedy the issue.
Thanks,
Mikel
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
SELECT TablePages, IndexPages, TableName
FROM sa_table_page_usage()
WHERE TableName IN ( Table1, 'Table2', . . . 'Free pages', 'Total pages' )
ORDER BY TableName
From this I was able to show that the database in question is 96% free.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Do you have the ability to recreate a remote database from scratch by downloading all the data into an empty database? (for example, after a catastrophic loss) If so, that might be an effective brute-force technique to shrink the database.
If not, then maybe it's a mechanism you should consider (see "catastropic loss" 🙂
Our product consists of a single server install and many client installs. The architecture uses SA as an embedded DB on the client side & SQL Server on the server side. The data on each client replicates to the server, so once the data has made it to the server, there's little need for it to stay on the client. In fact, we have a module that implements a data retention policy on the client which deletes all data that has replicated to the server & is older than a user configurable age.
The system architect is resistant to perform a DB rebuild on clients. It would probably take longer to go though that process than just uninstalling & reinstalling the client software takes. There seem to be no ill effects on performance from having a 25 GB database on that machine that is 96% empty.
If it were the server side, he'd have a different opinion, but once its made it to the server, the data on the client is just redundant. It's more important for the client to be collecting new data & sending it to the server than it is to take that database offline to shrink it.
There seem to be no ill effects on performance from having a 25 GB database on that machine that is 96% empty.
Are the remote databases doing backups? If so, I would think a huge empty database does have some impact on performance, simply as the backup of the database file will take longer... The same ought to be true for validation. - However, I can't tell whether that would be a problem or not.
User | Count |
---|---|
82 | |
29 | |
9 | |
8 | |
7 | |
7 | |
7 | |
6 | |
6 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.