为什么我的Access 2007数据库增长如此之多？

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.

我在一个Win32应用程序上工作，该应用程序已经开发出一个非常奇怪的数据库问题，直到它最终达到2 GB的文件大小限制。我们使用ADO连接到Access 2007数据库。该应用程序已经很好地工作多年，没有观察到这样的困难。如您所想，当它达到2 GB的限制时，数据库就会损坏。我现在有很多客户数据库被发送给我们进行维修 - 大小约为2GB。一旦压缩，它们就会回到<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.

我创建了一个小型数据库“checker”，它将所有记录中所有字段的内容相加，以便了解实际数据的存在量。在最近已经压缩的数据库上检查了这个新工具后，我认为该工具正常工作。所有膨胀的数据库每个都有不超过10 MB的数据。

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?

你能否建议我们如何拥有一个<10 MB的数据库，可以增长到2 GB？

A few remarks about what our app does:

关于我们的应用程序的作用的一些评论：

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

当ADO没有打开数据库时，使用DAO完成任何重组。
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".

为方便起见，某些记录便于删除和重新创建，而不是查找/编辑/删除。通常，此操作涉及5-30条记录，每条记录约为8K。这仅在用户按下“保存”时发生。
there are other record types that are about 70 KB/record but we're not using delete/recreate with them.

还有其他记录类型大约70 KB /记录，但我们没有使用删除/重新创建。
we use a BLOB ("OLEObject") field to store binary data.

我们使用BLOB（“OLEObject”）字段来存储二进制数据。

thank you for any insights you can offer.

感谢您提供的任何见解。

3 个解决方案

#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

MS Access文件非常容易膨胀。它们存储了大量的交易历史记录，并在记录删除期间保留了大小。当我使用Access数据库编写应用程序时，我会将常规压缩因素考虑到设计中，因为这是使数据库保持一致的唯一方法。在关闭时压缩可能会出现问题（取决于环境），例如用户迫使压缩程序中止，因为他们希望计算机在一天结束时完成关闭。同样，打开时紧凑可能会导致用户想要进入程序但却无法进入程序的令人沮丧的延迟。我通常会尝试组织紧凑型作为计划任务在一台永远在线的PC上完成，例如服务器。请访问该链接以获取更多信息：http：//support.microsoft.com/kb/158937

#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.

每次调用此函数时，数据库都会增长大约32KB。

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

我改为不经常使用DAO而不是ADO来做这个功能。

#3

So doing a little research, I came across a discussion about how MS Access files will grow until compacted, even when data is deleted. From this I infer that they are storing the transaction history within the file. This means that they will continue to grow with each access.

所以做了一些研究，我遇到了一个关于MS Access文件如何增长直到压缩的讨论，即使数据被删除也是如此。据此我推断他们将事务历史存储在文件中。这意味着它们将随着每次访问而继续增长。

The solution is compaction. You apparently need to compact the database regularly. You may want to do this on application close, instead of launch if it takes to long.

解决方案是压缩。您显然需要定期压缩数据库。您可能希望在应用程序关闭时执行此操作，而不是在需要很长时间时启动。

Also note that this means multi-operation changes (such as the delete then reinsert modified value mentioned above) will likely cause the file to expand more quickly.

另请注意，这意味着多操作更改（例如删除然后重新插入上面提到的修改值）可能会导致文件更快地扩展。

#1

#2