UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Bloating Db Size, Access 2016    
 
   
Everettc4
post Oct 17 2018, 01:05 PM
Post#1



Posts: 203
Joined: 12-June 06
From: Oregon


Hi all I have an interesting issue. I have a time and attendance database that has 4 primary users. Each morning one of the admin's imports time punches from various time clocks around the shop. maybe 10,000 time punches a day. After they do that they have a couple processes they run to verify and except hours then run production reports. After about a week the backend database bloats to over 2gig. one of the admins has to make a copy of the db then compact and repair then it returns to 147meg and the process begins again. I have moved the db to a Small business server and we do have one a couple tables linked to a SQL server, but I'm really curious what type of actions cause bloating like this??

Any help appreciated!!

Thanks

Everett
Go to the top of the page
 
RJD
post Oct 17 2018, 01:30 PM
Post#2


UtterAccess VIP
Posts: 9,475
Joined: 25-October 10
From: Gulf South USA


Hi Everett:

QUOTE
...what type of actions cause bloating like this?

There are a number of reasons. Rather than trying to go through them here, do a search on "why does my access database keep getting bigger" and you will get a number of hits with a lot of explanation. Since we don't have your db, it would not be possible to be specific here. You'll have to examine your db for any of the situations described in the search hits.

Good luck with this. This is always an issue as we build and use our databases.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kfield7
post Oct 17 2018, 02:58 PM
Post#3



Posts: 936
Joined: 12-November 03
From: Iowa Lot


While I agree there may be many causes for the bloat, from your description I might look at how the import is done. For example, is the data imported into a holding table, and once the final import is done, the holding table records are deleted? Is the holding table created on the fly and deleted in entirety when done? (that will definitely cause bloat, and one solution is to do all temporary tables in an external .accdb instead of the main one. Or, if the temporary table is in the front end, load a fresh copy of the front end each time it's run).

I'm assuming you have split the database, and each user has their own front end.
This post has been edited by kfield7: Oct 17 2018, 02:58 PM
Go to the top of the page
 
Everettc4
post Dec 18 2018, 05:52 PM
Post#4



Posts: 203
Joined: 12-June 06
From: Oregon


Hey thanks, guys, so I have found some other threads that suggested we have the database compact on close. I have selected that option and tested opening and closing and indeed it seems to work. Now we are wondering what the best method for opening and closing a database on a schedule so it can happen automatically at midnight? We have found a method to close the shares on our server so the locked DB shouldn't be an issue. Any recommendations on auto open close??

Thanks
Go to the top of the page
 
isladogs
post Dec 18 2018, 06:40 PM
Post#5



Posts: 1,141
Joined: 4-June 18
From: Somerset, UK


Automatically compacting on close may well help your bloating issue but can also lead to possible corruption and data loss
I would recommend you don't do this unless you BACKUP the database first.
Instead add code to compact only when the database reaches a certain specified size with an automatic backup done first.

kfield7 gave some very good advice about your import routine which sounds badly flawed.
Importing 70,000 records in a week should create some bloat but nothing like the amount you describe

You were also asked whether each user has their own copy of the FE on their own hard drive (ESSENTIAL)

BTW if your Access BE database exceeds 2GB, it may well become impossible to retrieve data leading to total loss of all data
You should probably consider moving all your BE tables to SQL Server





--------------------
Go to the top of the page
 
Everettc4
post Dec 18 2018, 07:08 PM
Post#6



Posts: 203
Joined: 12-June 06
From: Oregon


Thanks for the reply
I would recommend you don't do this unless you BACKUP the database first. We back up daily to an external NAS that is mirrored.
Instead add code to compact only when the database reaches a certain specified size with an automatic backup done first. I like the sound of that where would you add this code? any samples??

kfield7 gave some very good advice about your import routine which sounds badly flawed. Thanks, we tried temp tables same result
Importing 70,000 records in a week should create some bloat but nothing like the amount you describe

You were also asked whether each user has their own copy of the FE on their own hard drive (ESSENTIAL) of course we have 20-30 user on at a time, with 4 admins that process every morning.

BTW if your Access BE database exceeds 2GB, it may well become impossible to retrieve data leading to total loss of all data
You should probably consider moving all your BE tables to SQL Server..Most of the tables have been transitioned to SQL we have just a few that weren't so willing to move due to format and inconsistency of data that has been being pushed around since 1997 but we're still trying!

Thanks for the help

Go to the top of the page
 
Everettc4
post Dec 18 2018, 07:09 PM
Post#7



Posts: 203
Joined: 12-June 06
From: Oregon


Thanks for the reply
I would recommend you don't do this unless you BACKUP the database first. We back up daily to an external NAS that is mirrored.
Instead add code to compact only when the database reaches a certain specified size with an automatic backup done first. I like the sound of that where would you add this code? any samples??

kfield7 gave some very good advice about your import routine which sounds badly flawed. Thanks, we tried temp tables same result
Importing 70,000 records in a week should create some bloat but nothing like the amount you describe

You were also asked whether each user has their own copy of the FE on their own hard drive (ESSENTIAL) of course we have 20-30 user on at a time, with 4 admins that process every morning.

BTW if your Access BE database exceeds 2GB, it may well become impossible to retrieve data leading to total loss of all data
You should probably consider moving all your BE tables to SQL Server..Most of the tables have been transitioned to SQL we have just a few that weren't so willing to move due to format and inconsistency of data that has been being pushed around since 1997 but we're still trying!

Thanks for the help


Attached File(s)
Attached File  SQL_Links.JPG ( 35.4K )Number of downloads: 8
 
Go to the top of the page
 
AlbertKallal
post Dec 19 2018, 01:12 AM
Post#8


UtterAccess VIP
Posts: 2,748
Joined: 12-April 07
From: Edmonton, Alberta Canada


I suggest you turn of row locking feature.

I have a database, and it bloats from 6 megs to 120 megs with just one loop to edit the data.

If I turn off row locking, then after running, the database is the same size.

The option you want to set, flip is this one:



Unfortunately, this is a access client setting, not a application setting.

You can with start up code “change” this setting, but it will not take effect until next time you launch the application.

Reducing bloat by a 100 megs for one simple loop on the data is “huge”. So I would consider this option.

Access does not have “true” row locking. So what this feature really is a “automatic bloating” feature. Any time you edit a record, Access will expand the record size to that of a database page. Since access has page locking, and you ALWAYS are stuffing on one record as the size of a database page, then the result is in a very cruel way row locking, but at the cost of each record taking up the space of a whole database page.

So this “Mr. Bloating” option can get you true row locking in Access, but at a really large cost in the bloating deparment.

Note that this feature is 100% separate from the form locking features that you may, or may not be using. While the bloating row feature can solve some issues, in 99% of cases, this feature is not required, and I would thus consider turing it off.

To turn off the “Mr bloat” locking feature, you can execute this in your startup code:

CODE
   SetOption "Use Row Level Locking", 0



Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
Everettc4
post Dec 19 2018, 01:41 PM
Post#9



Posts: 203
Joined: 12-June 06
From: Oregon


Interesting how does row locking effect, multiple users?


Go to the top of the page
 
isladogs
post Dec 19 2018, 02:21 PM
Post#10



Posts: 1,141
Joined: 4-June 18
From: Somerset, UK


Do be aware that if you switch off record level locking using code or in client settings, it stays switched off for all your databases until you reverse that setting.
Although Albert is quite correct that it will massively improve your bloating issue, it will have other side effects.
If disabled, records are locked at the page level.
In a multiuser environment, this increases the risk of write conflict errors.
See this MS article Help Access run faster
This issue is discussed about half way through the article

Whether or not you decide to disable record level locking, I still suggest you review your whole import routine to make it more efficient
This post has been edited by isladogs: Dec 19 2018, 02:23 PM

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th March 2019 - 08:56 PM