|
Home > Archive > SQL Anywhere database > May 2005 > Why does DB size increase on deletes?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Why does DB size increase on deletes?
|
|
| Balaji Govinda 2005-05-16, 11:23 am |
| Hello,
I am using Sybase SQL Anywhere v7.0.4, and noticed that size
of the database increased when I deleted more than 10,000
records from a table. However, same did not happen on
Sybase SQL Anywhere v9.0.1.
Question is, is it a known defect fixed in v9.X? If so, is
there a EBF for v7.0.4? What is the workaround if any?
Any help is greatly appreciated.
| |
| Breck Carter [TeamSybase] 2005-05-16, 11:23 am |
| Here is Chris Keating's answer from April 13...
=====
In ASA8 and later, certain logs such as the checkpoint log are hosted
at the end of the database file. When the database is closed, a
checkpoint occurs and space attached to the checkpoint log is freed
and the file size is reduced.
=====
Breck
On 16 May 2005 08:48:48 -0700, Balaji Govinda wrote:
>Hello,
>
>I am using Sybase SQL Anywhere v7.0.4, and noticed that size
>of the database increased when I deleted more than 10,000
>records from a table. However, same did not happen on
>Sybase SQL Anywhere v9.0.1.
>
>Question is, is it a known defect fixed in v9.X? If so, is
>there a EBF for v7.0.4? What is the workaround if any?
>
>Any help is greatly appreciated.
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Balaji Govinda 2005-05-16, 11:23 am |
| Thank you. Does it mean that I need to upgrade to v8.X or
above? Isn't there any EBF or work around in v7.X?
Balaji
> Here is Chris Keating's answer from April 13...
>
> =====
> In ASA8 and later, certain logs such as the checkpoint log
> are hosted at the end of the database file. When the
> database is closed, a checkpoint occurs and space attached
> to the checkpoint log is freed and the file size is
> reduced. =====
>
> Breck
>
>
>
> On 16 May 2005 08:48:48 -0700, Balaji Govinda wrote:
>
> size >of the database increased when I deleted more than
> 10,000 >records from a table. However, same did not
> happen on >Sybase SQL Anywhere v9.0.1.
> is >there a EBF for v7.0.4? What is the workaround if
> any? >
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| David Kerber 2005-05-16, 11:23 am |
| In article <4288c0e0.3834.1681692777@sybase.com>, Balaji Govinda says...
> Hello,
>
> I am using Sybase SQL Anywhere v7.0.4, and noticed that size
> of the database increased when I deleted more than 10,000
> records from a table. However, same did not happen on
> Sybase SQL Anywhere v9.0.1.
>
> Question is, is it a known defect fixed in v9.X? If so, is
> there a EBF for v7.0.4? What is the workaround if any?
It's not a defect at all; it's just the way the rollback log works in
the different versions. Why do you need a workaround? it's not a
problem...
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Balaji Govinda 2005-05-16, 1:23 pm |
| I was asking for a workaround thinking it might be a defect.
How much size will increase, is there a calculation that I
can use?
> In article <4288c0e0.3834.1681692777@sybase.com>, Balaji
> any?
>
> It's not a defect at all; it's just the way the rollback
> log works in the different versions. Why do you need a
> workaround? it's not a problem...
>
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts
> in the newsgroups if possible).
| |
| David Kerber 2005-05-16, 1:23 pm |
| In article <4288d27f.38c9.1681692777@sybase.com>, Balaji Govinda says...
> I was asking for a workaround thinking it might be a defect.
> How much size will increase, is there a calculation that I
> can use?
Beyond the fact that it will increase enough to allow it to roll back
the transaction, I don't know. However, it won't continue to grow
indefinitely: Once that space is no longer needed after the transaction
commits, it will then be available for other uses, including the next
round of deletes, so as long as you delete as much data as you insert,
your database file size will stabilize at the size needed to handle all
the work you do with it.
>
>
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Greg Fenton 2005-05-16, 8:23 pm |
| Balaji Govinda wrote:
> I was asking for a workaround thinking it might be a defect.
> How much size will increase, is there a calculation that I
> can use?
>
Along with David's response, realize that if you have a process that is
doing MASSIVE amounts of deletes (or inserts) in batch on a regular
basis, you'd be better off to break those batches into smaller
transactions rather than a single one. The reason being that the
checkpoint log and rollback log need to grow to accomodate the larger
transaction. This can result in larger .db files as well as performance
degradation.
Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| John Smirnios 2005-05-17, 9:23 am |
| One minor correction: the undo log does grow proportional to the number
of operations in a transaction but the checkpoint log does not. The
checkpoint log grows to accomodate a copy of all pages modified since
the last checkpoint. If you want the checkpoint log to grow less, you
need to checkpoint more often, not commit more often.
-john.
--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering
Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
Greg Fenton wrote:
> Balaji Govinda wrote:
>
>
> Along with David's response, realize that if you have a process that is
> doing MASSIVE amounts of deletes (or inserts) in batch on a regular
> basis, you'd be better off to break those batches into smaller
> transactions rather than a single one. The reason being that the
> checkpoint log and rollback log need to grow to accomodate the larger
> transaction. This can result in larger .db files as well as performance
> degradation.
>
> Hope this helps,
> greg.fenton
|
|
|
|
|