Why is my Access 2007 database growing so much?

Question

I work on a Win32 application that has developed a very strange problem of the database quietly growing until finally it reaches the 2 GB file size limit. We use ADO to connect to an Access 2007 database. The application has worked nicely for years with no such difficulty being observed. as you may imagine, when it reaches the 2 GB limit, the database becomes corrupt. I have quite a few customer databases now that were sent to us for repair--all around 2GB in size. once compacted, they come back to < 10 MB.

we see some database growth over time but never growth on that sort of scale.

I made a small database "checker" that adds up the contents of all fields in all records to give some idea how much actual data is present. having checked this new tool on databases that have recently been compacted, I think the tool is working correctly. all the bloated databases have not more than 10 MB of data each.

We don't compact the database at app start. It has seemed to me that because we don't delete large amounts of data, compacting the database isn't something we "should" need to do. customers with larger databases (there are some but they are on earlier versions).

Can you suggest how we could have a database that should be <10 MB could grow to 2 GB?

A few remarks about what our app does:

• any restructuring is done using DAO when ADO does not have the database open.

• we do use transactions in a few places

• for convenience, certain records are convenient to delete and recreate instead of find/edit/delete. typically this operation involves 5-30 records, each about 8K per record. this only occurs when the user presses "Save".

• there are other record types that are about 70 KB/record but we're not using delete/recreate with them.

• we use a BLOB ("OLEObject") field to store binary data.

thank you for any insights you can offer.

Answer 1

MS Access files bloat very easily. They store a lot of history of transactions, as well as retaining size during record deletion. When I write an application with an Access database I factor regular compaction into the design as it is the only way to keep the database in line. Compacting on close can present issues (depending on the environment) such as users forcing the compact to abort because they want their computer to finish shutting down at the end of the day. Equally, compact on open can cause frustrating delays where the user would like to get into the program but cannot. I normally try and organise for the compact to be done as a scheduled task on an always on PC such as a server. Please follow the link for more information: http://support.microsoft.com/kb/158937

Answer 2

thank you all for your help. found where it happened:

var
  tbl:ADOX_TLB.Table;
  cat:ADOX_TLB.Catalog;
  prop:ADOX_TLB.Property_;
begin
  cat:=ADOX_TLB.CoCatalog.Create;

  cat.Set_ActiveConnection(con.ConnectionObject);

  // database growth here
  tbl:=cat.Tables.Item[sTableName];

  prop:=tbl.Properties['ValidationText'];

  Result:=prop.Value;

  prop:=nil;
  tbl:=nil;
  cat:=nil;
end;

each time this function was called, the database grew by about 32KB.

i changed to do this function less often and do it with DAO instead of ADO.