Sybase anywhere DB Rebuild activity frequency

[Former Member]

Hi Team,

I need information on frequency of rebuild of Sybase anywhere DB 16 . (Recommendations) .

Thanks
Prashant J

[chris_keating · Accepted solution]

Rarely except when moving to new version of SQL Anywhere that has a new database file format. That translates to moving to a new major version generally.

Perhaps you can expand on why you believe you need to rebuild and we can discuss specifics.

[Former Member · Accepted solution]

Thanks for reply ,

For example...If I drop  30  table with huge data , DB sapce will  not shrink. sybase suggeting

to rebuild DB to Shrink DB size . Please comment.

thanks

Prashant J

[former_member188493 · Accepted solution]

When data is deleted, the space is available for inserting new data, either for the same table or (when a page becomes completely free) for other tables. However, once the physical file has increased in size for new table pages it will never be decreased in size. To free up the space, you have to create a new (smaller) physical file and copy the data to it, then delete the original file. This is often called a "rebuild".

If you are planning to re-create those tables and re-insert the data, it is better if you leave the free pages alone because the act of adding new empty pages takes a bit of time.

[lucjan_chmura]

Hi Prashant,

just to add to these notes.

You need to figure it out yourself through monitoring/testing/experience when is the time to rebuild your db. And, just like Breck said, it can be 'never' , 'sometimes' or 'frequently'. And, btw. when you believe your database needs to be rebuilt frequently then most likely the problem is something else not extra space or fragmentation.

In some cases, with some applications (specially where there is lots of inserts/deletes ) you may find that rebuild really helps, but then if you know which table, you can use an alternative ("reorg' ) to just one table instead of rebuilding the whole database.

But, If you really want to rebuild it to gain your space back e.g. database has grown due to user's error etc., then you need to use dbunload.

There is no "dbshrink" etc.. in SQL Anywhere. Some vendors have this, but I imagine while database is being "shrinked" it still needs to be locked from all users. It's a different philosophy , if you need to rebuild your database frequently that usually means there is something else is wrong (users are using your application not the way it's designed for/ database design/ optimizer not making right choices.. etc..)

Hope this helps.

[Former Member]

Thanks Lucjan

[former_member188493]

For many databases the answer is "never". For other databases, the answer is "often"; for example, if millions of rows are inserted, deleted and/or updated with longer column values every day... in that case, a rebuild will reorganize the data. Some folks use the REORGANIZE TABLE statement on individual tables to avoid the outage caused by a complete rebuild.

In conclusion, the answer is "it depends".

[Former Member]

Thanks Carter