Split Database issues

[sdel_nevo]

Hi Guys



i have been having issues recently with an the linked database that our end users connect to growing to 1.5GB in size very fast.

this database has been split, each user (10 in total) has a front end form connected by linked tables to the backend database.

this has been working really well over the last 5 - 6 months but recently, the backend database size is growing to nearly 2GB 2 - 3 times a day with requires me to compact and repair the backend frequently.

is it possible that querys running on the front end forms can be coursing the backend database to grow this fast?

when i compact and repair the database is shrinks back to arount 7MB

[ItsMe]

I seem to remember complaints about something similar a few years back. It seemed to be (according to the person with the issue) related to a service pack on a certain Server product and or a feature built into a server product.

Do you have any saved query objects on the BE file?

I would start by taking a copy of the bloated BE and compare the system tables in it to the system tables in a copy that has been compacted.

[sdel_nevo]

Hi itsme

no the backend only holds the tables, no query objects are held there at all
the backend is held on 2008R2 server, this will be upgraded to 2012R2 very soon, it may be best for me to manage the situation as i am, and check when the upgrade has completed.

if not a service pack issue, not sure what i can do other than upscale this database to sql server, this fills me with dread really as i am a relative Noob at this

Steve

[ItsMe]

If you do not want to look at the System tables, you could try importing the tables to a new, blank DB. You could do an afterhours cutover. The whole thing should not take more than an hour. The fun part would be showing up first thing the next morning to ensure there is not a need to rollback.

If you do an import, you will want to study the existing relationships and referential integrity rules. You can use advanced options of the Import Wizard to bring these settings over to a new database. However, importing these may also import any corruption that you are trying to purge.

[sdel_nevo]

Hi ItsMe

I would love to check the system tables, but to be honest, im not sure what i would be looking for,
i will follow your advise to see if i can see anything obvoius

Kind regards

Steve

[ItsMe]

A bloated table might identify one or two tables that are causing the issue. I would look for an ID of an object within the bloated table.

[sdel_nevo]

Hi ItsMe

thanks for getting back,

i will check the system tables to see if i can find the issue

Kind regards

[NTC]

do you have Make Table queries? or something that is processing via temp tables? if so - it is doubtful these temp tables should be in the back end at all - - move them to the front end. Just a thought.

[sdel_nevo]

Hi NTC
Yes their are quite afew, I hadn't considered that. First thing Monday morning when I'm back in the office I will move these to the front end form

Many thanks for the suggestion
Steve

[orange]

As NTC pointed out, your backend only contains tables. If the backend is growing considerably, what cause the backend to grow:

a) data entry -seems too rapid a pace ; very rare
b) adding new tables; make table queries <------------------ Issue??
c) adding/storing images/blobs within the database

What exactly is the purpose of the make table queries?

[sdel_nevo]

Hi orange

I have temp tabled that hold data imported from csv files, these are then appended into the correct table if certain conditions are met, on Monday morning when I'm back at work I will move the temp tables into the front end as NTC suggested.

It's odd though that when I embed the linked tables back into a copied front end the database runs really well and the file size increase in minimal

I will update the front end and see how that runs

Kind regards
Steve

[ssanfu]

do you have Make Table queries? <snip>

Hi NTC
Yes their are quite afew, <snip>

The preferred method (better) is to create the table once. Then just delete the data in the table and use an append query to add the new data.

Constantly creating and deleting tables:
1) is slower
2) is a good indicator that the tables are needed.
3) is a good method to cause corruption of your database. Not IF, just WHEN.

[sdel_nevo]

Hi ssanfu

Many thanks for the details,that may be the way to go

Kind regards
Steve

[HiTechCoach]

Hi orange

I have temp tabled that hold data imported from csv files, these are then appended into the correct table if certain conditions are met, on Monday morning when I'm back at work I will move the temp tables into the front end as NTC suggested.

It's odd though that when I embed the linked tables back into a copied front end the database runs really well and the file size increase in minimal

I will update the front end and see how that runs

Kind regards
Steve

Stve,

I would not put the temp table in the front end either. You will have the same issue with the front end.

The Best Practice method for me is to use a work/scratch back end in the same folder as the front end on the local PC. You can delete and recraete it, copy a new empty one each time, each time or use VBA code to compact it.

[ItsMe]

...I would not put the temp table in the front end either....

Wouldn't selecting Compact and Repair on close within the options of the frontend be sufficient? I suppose there is not a guarantee that the user will ever close their application.