Hello,

I have a Pervasive.SQL 8.7 based application being used by a customer with a
database consisting of over 200 tables and using referential integrity. The
customer ran a purge function to delete a large number of records from
several tables. Everything works as expected, except that the tables got
larger, not smaller. One table went from 514MB to 949MB.

I know he can run a rebuild to reduce the file sizes, but why would the
tables get bigger during a delete operation?

Thanks,
- Greg

Ahh, the magic of shadow paging. Shadow paging is the internal
mechanism that the database engine uses to ensure that the files don't
get damaged if an update fails mid-stream. It uses free space pages
inside the file to hold copies of the data being changed, such that if
it fails, it can roll back to the previous version.

When you let a file grow slowly, the number of pages changed in each
transaction is small, so the number of free space pages in the file is
small. You can get a file to 500MB with only a hundred or so free
space pages.

However, when you delete data in bulk (like a purge), you are deleting
VERY rapidly, and possibly affecting records on MANY pages at the same
time. When the system transaction goes to write it to the disk, it
suddenly needs 100,000 free space pages to handle the surge of changes.

Since there are only 100 in the file, the database engine needs to add
another block of pages to satisfy the write operation. Where does it
get the space from? It grows the file.

You can prevent the massive growth by purging slowly -- as slowly as
the file grew. But usually, this is not practical, and people simply
rebuild files after the purge.

FYI -- This is one of the topics that are discussed in our Service &
Support course...
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Austin: Pervasive Service & Support Class - 05/2006 ***
*** Chicago: Pervasive Service & Support Class - 07/2006 ***

Greg Doherty wrote:

Thank you. That certainly explains it.

Thanks,
- Greg


"Bill Bach" <goldstar (AT) speakeasy (DOT) net> wrote