Reducing database file size after removing images

[Westy]

I have removed a large number of images from a Sybase ASA database file and would like to reduce the size of the file. Interestingly, removal of the images actually increased the size of the file, instead of decreasing it.

With Firebird I could reduce the file size by backing the file up and restoring it. With Sybase when I do a backup and restore, the resulting files (two files including the log) are still the same size as before the backup. How can I reduce the size of a Sybase file after removing images?

[Jan Aleman]

You can rebuild your database to accomplish this. More information can be found in the ASA documentation.

ASA SQL User's Guide
Importing and Exporting Data
Rebuilding databases

Databases do not shrink if you delete data. Instead, any empty pages are simply marked as free so they can be used again. They are not removed from the database unless you rebuild it. Rebuilding a database can reclaim disk space if you have deleted a large amount of data from your database and do not anticipate adding more.
If you expect your database to grow again there's no need to reclaim your diskspace.

[Westy]

IMPORTANT: Make sure you have backups of your .db and .log files before trying the steps below.

Thank you for pointing me in the right direction. I was able to "Rebuild" a Sybase database file reducing its file size from 110MB to 2MB (on a Windows XP Professional computer). Below is a very literal listing of the steps required. I hope this will save others time (and that it will later remind me how to do it):

(note: the steps would be different for a database involved with replication)

1. started Adaptive Server Anywhere as a Windows Service
(described at http://forum.servoy.com/viewtopic.php?t=1800)
2. opened Sybase Central
3. right-clicked Adaptive Server Anywhere 9 and selected "Connect"
4. entered user id and password (default is "dba" and "sql")
5. clicked "Database" tab
6. clicked "Find" button
7. double-clicked "servoy_repository"
8. clicked "'Browse" button
9. selected name of database file to be rebuilt
10. clicked "OK" to close dialog
11. right-clicked name of database file in Sybase Central
12. left-clicked "Unload database..." (not very intuitive)
13. read directions and clicked "Next" button
14. selected name of database file and clicked "Next"
15. selected option to "Unload and reload into a new database"
16. clicked "Browse" button
17. selected a folder and entered a file name for rebuilt file
18. clicked "Next" button
19. clicked "Next" button again
20. selected "Unload all database objects" (the default)
21. clicked "Next" button
22. unchecked "Connect to the new database" (the default)
23. clicked "Finish" button
24. voila! Tiny versions of my .db and .log files were created.

The most difficult part was knowing that in order reduce the size of a Sybase database file I had to "Rebuild" the file by "Unloading" and "Reloading" the database. Of course, before I followed the above steps, I tried to "compress" the files, which was a mistake because a Sybase "compressed" file cannot be updated.

Words, words, words...

[Jan Aleman]

Glad it worked and thanks for your step by step instructions!

[Westy]

Caution on my step 17. Read this before trying the above. I entered contactsrecovered.db as the name of the rebuilt file and c:\_test as the destination. I later deleted the contactsrecovered.db file when I found that the rebuild succeeded.

However, now every time I start the Adaptive Server Anywhere Service a new file named contactsrecovered.log is created in the Servoy database folder. Trying to track down why this was happening I went into Sybase Central and opened a connection to the servoy_depository and my contacts database file. Within the contacts file there is a subfolder named Dbspaces. Within that folder there is an item named SYSTEM with a path C:\_test\contactsrecovered.db even though I have deleted that file. I notice that all the other databases have a similar SYSTEM item within their Dbspaces folder. However, their path is C:\dev\j2db\build\database\empty_crossplatform\<databasename>.db .

Everthing seems like it is working fine, but that contactsrecovered.log file keeps being recreated in the Servoy database folder everytime I restart the Adaptive Server Anywhere Service. I was not confortable with that so I completely uninstalled everything Sybase and reinstalled. However, the contactsrecovered.log file still keeps being created in the Servoy database folder every time I restart the Adaptive Server Anywhere Service. Reading the Help file, I am concerned about that c:\_test path that is still in the Dbspaces folder. Their description of Dbspaces makes them seem pretty important. I hate to have one pointing off in the wrong direction.

How can I correct the Dbspace folder SYSTEM item path? The Help file says you can right-click it and delete it. However, when I right-click it the delete option is greyed out. If I select the option to create a new item to replace the one with the wrong path, I am not allowed to do so because it states that there is already an item named SYSTEM.

Seems I have somehow stumbled into the wonderful world of Dbspaces and do not know how to get out.

[Jan Aleman]

You can change the location where your logfile is written to by choosing Tools -> Adaptive Server Anywhere 9 > Change Log File Settings.

[Westy]

Using the "Change Log File settings..." I did not see a way to change the path of a Dbspace that has the name "SYSTEM", so I replaced my two .db files and their .log files with backups. However, I now notice that the replacement files have a Dbspace named SYSTEM that points to the default database folder, while the crm.db and all the other .db files have a Dbspace SYSTEM path of C:\dev\j2db\build\database\empty_crossplatform\<databasename>.db

There is no c:\dev folder on my computer, so I am curious what that path represents. I notice that all the .db files have a .log file in the default database folder. Why is there also a need to have this other c:\dev... path? I am concerned because my two .db files are now the only ones that do not have this longer Dbspace SYSTEM path. Their Dbspace path is just: <databasename>.db

[Jan Blok]

It seems sybase does store the db file creation directory in the db file (for unknown use...)