DB Bloated - Compact and Repair (1 Viewer)

[jsdba]

Access Devs,

I have a database application that has been in use for over a year. Only part of the application was used in 2016, the part that creates 80% of the data. As of Jan 1, the entire application is being used. At the end of Dec 2016 my backend file was .8g. Since Jan the back end has been growing consistently at about 20mb a day. Yesterday i did a compact and repair at 1.24g, db shrank to 1.06g. Today i saw that the database grew to 1.5g. Its virtually impossible that users would create that much data in one day. Could compact and repair have any effect of how the db grows? I'm in a desperate situation here!!!

 

[jdraw]

Compact and repair is the proven method to recover space that you have used for edits/deletes etc.
Does the application create and delete temporary "things"? Do you copy tables/queries etc?
Is there any development going on in this database?Do you store pictures or documents within the database?

 

[jsdba]

Compact and repair is the proven method to recover space that you have used for edits/deletes etc.
Does the application create and delete temporary "things"? Do you copy tables/queries etc?
Is there any development going on in this database?Do you store pictures or documents within the database?

I do not store temp objects(tables, qry, etc) but i do store pdfs (ole) but not a lot. There is no more than 50 in the database and none more than 1mb. I've read that not cleaning up after opening dao recordsets can cause db bloating but im pretty confident im cleaning up everything i open a recordset. rs.close and set rs = nothing. I tested another compact and repair a few minutes ago and db went from 1.5g to 1.25g. Is there anything else you can think of that can cause this bloating?

 

[jdraw]

Is there a reason to store the pdf as ole in the database?
Many would have file system coordinates in a table and a hyperlink to the pdf on the file system.

 

[jsdba]

Is there a reason to store the pdf as ole in the database?
Many would have file system coordinates in a table and a hyperlink to the pdf on the file system.

Yes because i print in the image on a report, which is really an invoice, the ole is a scan pdf copy of a receipt. its essential to the invoice report.

 

[The_Doc_Man]

While there can be many reasons to store OLE stuff in your DB, it is ALMOST always a terrible idea because of the ability to hyperlink to any document that makes it far easier - and cheaper - to find/open the file in question.

A growth rate of 20 Mb per day is unsustainable without some attention. That is 2000 Mb (i.e. 2 Gb) in 100 days, and at that point you won't be able to do a Compact & Repair.

Is there a chance that your PDFs are being replaced dynamically? I.e. take one out, diddle with it, replace it? Because if that is what is happening, you DEFINITELY do not want to keep the OLE files inside the DB. The space from which the prior "version" of the file was stored as an OLE entry CANNOT be reclaimed without a Compact & Repair.

Let's put it another way. If you are not dynamically updating & storing your PDFs, and if you can't figure out where else your space is going, then it is SERIOUSLY time to think about something like SQL Server or My SQL or anything else that can break the 2 Gb file-size barrier that is looming ahead of you.

EDIT:

Yes because i print in the image on a report, which is really an invoice, the ole is a scan pdf copy of a receipt. its essential to the invoice report.

Essential? Doesn't matter if it is what is killing your app. If there is a way to turn that into another kind of image, like a .JPG or .WMF or .TIF or even (shudder) .BMP, you can keep it external to the file and still reference it for the purpose of generating a report.

And in fact, that tells me where your space is going. Your original OLE gets DUPLICATED (internally) for every page of the report that contains that embedded object. That is, to format the report, a memory copy has to be built that contains the information of the OLE - but since reformatting for printing purposes would alter the original OLE if you did that, Access has to make a copy in the temporary space used by the report.

 

[jdraw]

You could schedule a compact and repair to run at a certain time every so many days.

Can you describe the invoicing creation/adjustment in a little more detail?

Do you have an pdf/image of a blank invoice? Then, based on Order, you adjust/modify the blank pdf
to create an image of the invoice. I haven't done this specifically so am interested in the process.
You're basically keeping a copy of the printed invoice? Seems it could still be stored on the filesystem.

OOps I see Doc has commented while I was posting.

 

[jdraw]

I found this post via search. (see post #6)

Galaxiom may have more details on PDFtk and it's use.

 

[CJ_London]

From your description, I suspect this is not the case but is the db split? and is the application multi user? and if so, does each user have their own copy of the front end?

it should be split and if multi user, each user should have their own copy of the front end.

At least then you will be able to tell whether the bloat is being caused by data (the backend) or coding (the front end) and if multi user, which user/s are causing the problem.

 

[jsdba]

Based on my own testing and what you guys have replied with i know for sure that its the Ole causing the db bloat. We "ran" another set of invoices/report containing Oles and BAM db is now 1.8g . I made a copy of the DB and deleted all the Ole records, did a compact and repair and DB is down to .8g

The_Doc_Man - thank you for shed some light on how Ole works. I wasn't familiar with them and my was reluctant to even use them. We're migrating to SQL server within the next 3 months so that's going to solve our 2g limit problem.

jdraw - invoice works like this, user drop a scanned pdf copy of an receipt into the database as an ole from their desktop. The ole field is embedded in the report as an image.

CJ_London, db is split and each user has their own copy of the front end.


I appreciate all the responses, ultimately i have no choice but to discontinue to use of ole

 

[The_Doc_Man]

I concur regarding OLE: As long as you use OLE, SQL Server will NOT solve all of your problems.

Store file references and see if you can't convert these receipt images to something for which you could place a file reference in the .Picture property of a report image control. From the printed page, there will be no way for you tell the difference between a PDF image and some other kind of image. I.e. once it is in print, who CARES from where it came as long as it is accurate?

I know that most scanners CAN create a .JPG or .BMP in place of a .PDF, but I have not used a PDF image this way before. I have used .JPG more than once.

EDIT: I see in the post referenced from jdraw that an image control CAN take an image-oriented PDF, so if you can't manage some other image format, just remember to keep the actual file elsewhere than inside the DB - which is what you have decided to do anyway.

Note that there is no technical issue with having a shared sub-folder set aside as the repository for image files. What matters when loading image controls is that the person generating the report must be able to "see" the path to the image file. There COULD be policy issues and space issues - but it should not be a technical issue.

 

[Lightwave]

Glad you have identified an option to improve things

If you do need to do automated compacts and repair here's how I have done it in the past.

rounduptheusualsuspects.org/?p=1386