Back-end bloating when updating

[MushroomKing]

Hey guys! Whatsup?

Ive been trying to find out whats going on all day, and its still a mistery to me.


- I have a split database
- A form with and On Timer event, running update and delete queries and so on.

Everytime the Timer event cycle ends, my backend is growing.
The amount of records stays the same, there is no double data or whatsoever.

When i compact the backend, it's shrinks right down to a normal size
(10mb backend size, growing 50mb every cycle)

Why is this happening? Where should i look to find the problem and solution.
I know i can compact it somehow, but why is it growing in the first place?

THANKS IN ADVANCE!


NOTE: I DO have an ODBC table connection also!!!

 

[Minty]

Delete queries sounds a bit ominous - and is probably the cause. Every time you create and delete a table or records the space consumed is not recovered without a compact and repair.

If you really have to do this frequently as part of your processing I would suggest a "Side End" which is a local temporary database file that you do your temporary processing in. You create it then delete when you are finished.

Lots of deletions isn't normally necessary though, whats the big picture?

 

[gemma-the-husky]

given that a backend (or front end) can grow to 2Gb, anything below 1Gb is hardly a problem. Just check weekly and do a C&R when you think it needs one. I would copy the database before you run the C&R

 

[MushroomKing]

Thanks man! Side-end sounds like an option. Anyway interesting for other applications also. Will try!


Thanks for answering gemma, but it's growing 50mb every 2 minutes....
And there are NO records ADDED...

 

[The_Doc_Man]

Here is the mechanism for back-end bloating after updates.

1. You did a Compact & Repair so everything is tightly packed like a sardine tin can. No space left over between anything.

2. You start an update. The record you update is (in effect) copied to a work area where you (Access) change the contents of some fields. The individual update action on that one record is ready - but cannot be used yet because...

3. The rule in access is that it starts the operation but cannot commit until it reaches the end. Have you ever noticed that if you get an error (like a key violation for example) you get a message about how the proposed action cannot be completed? In particular, if you used CurrentDB.Execute, the description says if it fails, there is an automatic rollback?

4. If you have to do a rollback, the fact that you CAN do one means that the original records temporarily co-exist with the new ones. So you cannot just overlay the records. Further, if any string was updated, there is no guarantee that the new copy would fit in the same space anyway. Oh, Access could test for that, but the odds are 50/50 that it wouldn't fit, so they skip the test and just always make that new copy. Which means that in the final phases of an update query, you have TWO copies of the records that were changed - one to be removed and the other to be added. Net change in number of records is still zero when done.

5. The last step is that the old record, which is part of a linked list, is unlinked from the table and the corresponding new record is linked into the table. But the only things that are changed (in this phase) are the links. The modified record already occupies its own space. What happens with the old records is that THEY are left dangling, still occupying the same space but no longer tied to a table. Like the holes in a Swiss cheese, they are empty but their space is still part of the whole. And it is those gaps left behind by unthreading the now-obsolete records that comprises the latest round of bloat.

6. So... you get to do the next round of C&R.

 

[Cronk]

@Mushroomking


One thing you can consider to handle the bloat is use the option of automatic compact on close. In your backend, go to File | Options | Current Database and check the particular setting.

 

[The_Doc_Man]

Cronk's idea is not bad, but be absolutely sure you have no one else in the BE at the time you try to do a BE C&R because otherwise you will fail due to non-exclusive access.

Also I'm not sure that the BE will automatically C&R correctly from the FE because if the BE is linked, it is open (from YOUR FE) and therefore might not count as exclusive access. If the table is dynamically linked, you can overcome this. But for static links, the table is open already - from YOU running the FE.

 

[theDBguy]

Cronk's idea is not bad, but be absolutely sure you have no one else in the BE at the time you try to do a BE C&R because otherwise you will fail due to non-exclusive access.

Also I'm not sure that the BE will automatically C&R correctly from the FE because if the BE is linked, it is open (from YOUR FE) and therefore might not count as exclusive access. If the table is dynamically linked, you can overcome this. But for static links, the table is open already - from YOU running the FE.

Besides the above points, I highly doubt it that the COC option in the BE will work at all. I believe it only works if you physically/manually open the BE file for the option to trigger an action. Simply opening a linked FE file will not trigger the COC feature in the BE. At least, I don't think so...

 

[Deleted member 147267]

I believe you would need a db whose sole purpose was to exclusively open the target BE via code. If it can, you can compact it from that db. The only reason I suggest a separate db is to pretty much guarantee there's no active connection to the target db via linked spreadsheets, forms and the like. Even if this "compactor" db had a form to facilitate this, it wouldn't be bound to anything, but IMHO a form would be preferable to just running code on open, such as by AutoExec macro. There are ADODB and other methods posted out there for doing this.

 

[The_Doc_Man]

theDBGuy - I wasn't sure about CompactOnClose of the BE from an FE open either, but wasn't completely sure. Thanks for bringing up the point.

Micron also raises a good point. An "isolated" DB that can perform the C&R on an arbitrary file would be preferable to having to go through "disconnection" gyrations for a related DB that has active links.

 

[Cronk]

Doc, there's no problem setting the option on the BE for compaction on close. It only happens when no linked table is open.

In the one app I did set this up (and I did use the so called side db for processing), there was still some bloat. When a user closed out while one or more others were accessing the BE, compaction did not occur without any warning message. It was only when the last user closed out, that compaction occurred.

The only down side to using the option for automatic compaction on close that I could see, was if the compaction was lengthy and someone wanted to use the application, they were prevented until compaction completion. This was not a worry for me as the database was open all work day and only closed when the last user was leaving at the end of the day.

 

[The_Doc_Man]

there was still some bloat

Yep, and always WILL be SOME bloat if you are updating records in the main BE file via the mechanism I presented earlier.

 

[Solo712]

@MushroomKing

Hi there,
It is not effective or wise to compact and repair on close every time by setting the "C&R on close" switch permanently. On the other hand I would not recommend keeping a big bloat of a file.
You can set the limit on the size of the Back End and execute automatically the C&R by a small piece of code when that limit is reached.

Public Sub SetCompactSwitch()
     Dim fl As Long
     
     fl = FileLen("MyBackEndFileName") / 1024000 'size in megabytes
     DoCmd.SetWarnings False
     'set the limit to trip the switch to 20Mb 
     If fl > 20 Then    
         Application.SetOption ("Auto Compact"), True
     Else
        Application.SetOption ("Auto Compact"), False
     End If
     DoCmd.SetWarnings True
End Sub

Call this sub as the last thing before closing the application. The C&R will execute the next time around automatically.

Best,
Jiri

 

[Cronk]

Application.SetOption ("Auto Compact"), True

Does that not set the front end for compaction?

 

[Solo712]

Does that not set the front end for compaction?

Yes, you are right. I am using this code only on unsplit databases. I did not realize that the Application object here refers exclusively to the Front End object. I guess, one would need to open another Application object for the Back End and set the options there. Will play with it.

Best,
Jiri

 

[Cronk]

Perhaps

application.compactRepair(filePathName)

 

[Solo712]

Perhaps

application.compactRepair(filePathName)

I think the method takes two arguments, source and destination. I have read some people complaining it does not work for them. One condition is that the source file has to be closed and unlocked. At any rate, I presently use DBEngine.CompactDatabase and for safety reasons do the switching manually. (I thought I was going to be able to use the SetOption but now it looks iffy, because of a slalom btw between Application objects). At any rate the following method works fine with a manual switching of the files.

Private Sub Compact_and_Repair()
    Dim oFSO As Object
    
    CRFlag = True
    On Error GoTo Cr_Error
    'Flush the cache of the current database
    DBEngine.Idle
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    If ChkFE Then   '<< front end C&R 
         destpath = txtFECDBPath
         'overwrite old file
         If Dir(destpath) <> "" Then
             Kill destpath
         End If
        'Create a file scripting objects that will backup feont end db
         oFSO.CopyFile SourceFEpath, destpath
        
         'Compact the new file, ...
          Name destpath As destpath & ".cpk"
          DBEngine.CompactDatabase destpath & ".cpk", destpath
          Kill destpath & ".cpk"
     End If
     
     If ChkBE Then  '<< Back End C&R
         destpath = txtBECDBPath
         If Dir(destpath) <> "" Then
             Kill destpath
         End If
        'Create a file scripting objects that will backup feont end db
         oFSO.CopyFile SourceBEpath, destpath
        
         'Compact the new file, ...
          Name destpath As destpath & ".cpk"
          DBEngine.CompactDatabase destpath & ".cpk", destpath
          Kill destpath & ".cpk"
     End If
     
     Set oFSO = Nothing
     
     Exit Sub
Cr_Error:
    ToErrLog True, "frmCRDB - Compact_and_Repair()"
    ToErrLog False, Err.Number & "-" & Err.Description
    MsgBox " Operation failed ! -> " & Err.Number & "-" & Err.Description & vbCrLf & _
                   " See Error Log for details ! ", vbExclamation, "Compact & Repair failed !"
    CRFlag = False
End Sub

You need to make sure that you have exclusive access to the Back End to do this otherwise it fails.

Best,
Jiri