Folks,

We are using SQL Anywhere as an embedded database server using the capi to communicate with the dbeng12 process. For our solution we treat database files as individual data stores for each users' projects. As in, each user's project gets its own database file.

Obviously, as the size of a user's project grows, so does the database file. In addition, some operations increase the file size to do some work, but then don't consume all of the extra space added...

What I want to know is if there is an appropriate way/time to get the database engine to take out the excess space in the database file to leave the user with a file that is as small as possible. Is it, for example, something that would be appropriate to do when the user is closing their project after making changes? What downsides would there be to doing something like that? Could it be slow for large databases for example?

Cheers, Dan

asked 02 Apr '13, 23:57

Dan%20Cleyne's gravatar image

Dan Cleyne
486101627
accept rate: 25%

edited 03 Apr '13, 16:48

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272


Database files will only grow.
To shrink it again you will have to unload/reload the data into a new file. This process is time consuming and with nearly linear effort compared to the database size.

If you are not encrypting the database file, you can compress it after usage (zip), using this approach you would gain much more space than just removing the empty pages.

permanent link

answered 03 Apr '13, 03:24

Martin's gravatar image

Martin
8.6k116151237
accept rate: 14%

Thanks for the advice guys,

I'll take that back to the developers and see what we can come up with.

The idea of compressing (using zip or something like it) when the user closes the project might well work.

Cheers, Dan

(04 Apr '13, 18:30) Dan Cleyne

Free space is good, because when new data is inserted the server doesn't have to grow the database file... it just uses the empty pages it already has.

Generally, unless the amount of unused space is so extreme that it is causing problems (drives running out of space, backups taking ten times longer, etc), shrinking databases just for the sake of reducing the file size is a waste of time... especially if the makes administration of an embedded application more complicated.

If you want to unload and reload to reorganize the data, that's another discussion... but you might want to ALTER DBSPACE ADD after that to grow the file again to accommodate future growth.

permanent link

answered 04 Apr '13, 16:02

Breck%20Carter's gravatar image

Breck Carter
26.2k430600863
accept rate: 20%

Alternatively, consider compressing your fastest growing tables to begin with. I have no idea what the performance penalty would be, but seeing as each user has his 'own' db file, you should be fine. If memory serves me right, compressible tables came out in ASA10

permanent link

answered 03 Apr '13, 06:31

Liam's gravatar image

Liam
36191118
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×10
×9

question asked: 02 Apr '13, 23:57

question was seen: 786 times

last updated: 05 Apr '13, 07:12