System resource exceeded: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
(cleaning up info since this is fixed; if anyone still gets this error let Tina know)
Tag: Replaced
Line 1: Line 1:
{{Bug}}
{{Fixed Bug}}


The following error message is display when sending: '''Microsoft Access - System resource exceeded'''
This problem was fixed with [[CCMDB.accdb_Change_Log_2019#2019-12-07]].  
*Internally this is also noted in the dbengine object as error number 3035.
*The error occurs specifically when the send program runs [[Query send check centralized is owner]].  


== Interim work-around: Pagasa will send for a collector who gets this error ==
See page history for details, deleting from here so they don't pollute the searches.
*Nov 28.19- '''Sending only allowed between:  1430 to 1530 daily'''. [[ Designated sending times]]
*'''NO SENDING or ADDING NEW DATA to your CCMDB records after 1530 daily''' ---until further notice.--[[User:TOstryzniuk|Trish Ostryzniuk]] 11:43, 2019 November 28 (CST)
**'''Collector''':
***finish going through all data entry for that day that would benefit from Patient Copier since you will need to delete records as part of this
***run [[Pre-send Checker]] and correct all error that were identified
***run [[News and backup]]
***Try sending-if successful nothing to do.
***If '''NOT successful'''  and you get ''System resource exceeded'' error message, stop trying to '''send and send Pagasa an email or call each time this happens.''' This communication piece is very important because Pagasa has no way of knowing if you: (a.) did not send or (b.) had error when you tried to send.
**'''Collector''': must coordinate with '''[[p:Pagasa Torres | Pagasa]]''' so that Pagasa can send your completed files. You must work very closely together on this so that no records are lost.
**'''Collector & Pagasa''': establish a '''phone call or email between the two of you to let Pagasa know you could not send'''
**'''Collector''': close out of Access and leave Access closed.
**'''Pagasa''': From Regional Server\Data folder the ccmdb_data.mdb COPY the collector ccmdb backup that you will sending to you C:\CCMDB\data folder
**'''Collector''' '''must check their email''' for notification from '''[[p:Pagasa]]''' that they can proceed in the follow order to:
***(a) '''delete COMPLETED files''' in their CCMDB
****you must delete the completed files because otherwise it will be impossible otherwise to distinguish between the completes that Pagasa sent, and the completes Pagasa has not sent for you yet.
***(b) '''do News and Backup''' 
****This will help reduce the number of records being kept on laptop that no longer need to be there.
***(c) '''can proceed to enter or add new data into their CCMDB'''
 
== Date issue started with each laptop ==
*STB_MICU-Dec 3, 2019 - first incident
*STB E6 - Dec 2, 2019 - first incident
*STB CICU - started Nov 27, 2019, also on and off can send or not.
*GRA ICU - started Nov 27, 2019
 
currently being done for following locations:
*GRA_N5/S3, HSC_B3/D5, HSC_D4 - Nov 25.19
*GRA_N3 - started Nov 21, 2019
*HSC_H4_h - started Nov 12.19 - able to send Dec 2nd & 3rd.
*HSC_D4 - started Nov ? not sure what date.
*HSC_A4-started Nov 7.19
*STB_MEDB - B5_IMCU - started Nov 5.19 - done intermittently as there are some day they can send. [[User:TOstryzniuk|Trish Ostryzniuk]] 19:02, 2019 December 2 (CST)
**Alana able to send Monday Dec 2.19 in AM and also at 1439 on same day.  Go figure....
**Pam able to send without error November 26, 2019 for this one day only.  Go figure.
**Allyson worked Dec 4, got error message.
*STB_MedD - E5 - started Sept 2019
**will try having Debbie send Dec 3.19 in AM to see if it works.
***Did not work.
 
== Observation - A new build of Windows10 was deployed at around the same time this became more common again ==
* In order to find out the running Windows 10 version do this:
** Press the Windows key + R
** Type winver
** Press Enter
** The result will be the About Windows window with information on the current running build. e.g: Version 1809 (OS Build 17763.720)
** Source:  https://www.ilicomm.com/how-to-find-out-which-build-and-version-of-windows-10-you-have/
 
== Observation - Timing of this becoming a problem again ==
*Pagasa observed that this became a problem again around the same time we changed the schedule of [[PHI Loader.accdb]]. We discussed possible connections but could not think of anything causal. Just putting this down here in case someone else can think of the connection. Ttenbergen 15:34, 2019 September 12 (CDT)
***Pagasa and Tina had a look at STB_MEDD (Debbie’s) data, and it possibly seems to be a matter of there really being too many records. MS Access is reaching its limit. For testing I tried to send with the first half of your patients and with the second half, and each works. Of course that is not a solution, but it confirms that the volume is the issue. In the long term we need to find an alternative to MS access, and to bring down the backlog, but that won’t be quick.
 
== Solution approach: copy those parts centralized to local ==
2019-12-09: Copy those parts of of CFE L_Log that are needed by [[Query send_check_centralized_is_owner]] to local, do comparison, then delete them. This way the query doesn't need to run across the network.
 
== Solution approach: Purging some old data from centralized ==
I made a copy and deleted records:
* all child records before 2015-01-01 (ie kept L_Log complete, but deleted corresponding records in L_TmpV2 etc). This shrunk centralized from 1GB to 618MB. However, it did '''not''' solve the problem.
* all records before 2019-01-01 (incl. Log purge)
** this would create a problem because [[Query send check centralized is owner]] would no longer be able to conclusively check for records that already exist in the db, ie. someone accidentally uses Pat_ID 100 rather than 1000)
 
== Solution approach: Query size limiting ==
The error happens when trying to run [[Query send check centralized is owner]]. That query actually ties into the [[Centralized data.mdb]]'s L_Log. This makes for a very large record set, and have a max size of 1GB rather than the general Access limit of 2GB.
 
* 13:29, 2019 September 4 (CDT) - optimized the query further, seemed to eliminate error for now, but error has recurred since
 
== Solution approach: disconnecting wifi ==
Darryl from STB desktop had suggested that turning off wifi might fix this problem. Since they don't actually have wifi in their area this is easy to try. This worked during his tests, but they have had additional problems sending after that.
 
During additional tests at HSC Tina found out that sending is faster with wifi disabled, so she added some lines to the sending code that disconnect wifi at the start of sending.
 
== Solution approach: Maxlock setting ==
*https://access-programmers.co.uk/forums/showthread.php?t=285553 sugggests to apply the following
**DBEngine.Idle dbRefreshCache
**DbEngine.SetOption dbMaxLocksPerFile, 1000000
Tried, doesn't solve the problem
 
MaxBuffer
 
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Jet 2.x
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Jet 3.x
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0
 
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Jet 3.x
 
MaxLocksPerFile
 
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE - conf 1000000
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Jet 3.x
 
== Solution approach: Microsoft Hotfixes ==
* https://support.microsoft.com/en-us/help/2760394/description-of-the-access-2010-hotfix-package-ace-x-none-msp-october-3
** Hotfix is no longer available, suggests we use newer software.
** confirmed this is not installed
 
* https://support.microsoft.com/en-us/help/2726928/system-resource-exceeded-error-message-when-you-perform-a-query-in-acc
** https://support.microsoft.com/en-us/help/2553447
 
Noticed that we may not be getting Office updates installed  ([https://support.microsoft.com/en-us/help/4501270/may-2019-updates-for-microsoft-office newest] not present)
 
==Status==
As of 09:48, 2019 September 4 (CDT) this is still a problem, and recently more of a problem.
 
Next things I will try:
* try to send from local to local - the query works fine when I run it while connected to a local version of CFE
* updating ccmdb.mdb to .accdb; if that is the solution we will also need to update many scripts and VBS/VBA programs.
 
=== Solution Approach - Access 64 bit ===
* https://stackoverflow.com/questions/34184018/does-upgrading-to-64-bit-access-fix-system-resource-exceeded-error
** Using MS Office 64 bit?
*** emailed Brandon to see if this might be possible to test
**** as of 2020-05 eHealth seems to paln to move to Office 360, so this may well be moot; also, the problem was resolved by differently so not pursuing this further for now Ttenbergen 18:28, 2020 May 5 (CDT)
 
== Related articles ==
{{Related Articles}}
 
'''Considerations'''
 
* https://stackoverflow.com/questions/34184018/does-upgrading-to-64-bit-access-fix-system-resource-exceeded-error
** Is it possible to update to 2010 64bit before going accdb? 
 
* https://stackoverflow.com/questions/19550367/access-2010-system-resource-exceeded
** 32/64 Bit issue? 
** Forms too big?
** Other programs using lots of memory?
** Windows 10 over Windows 7?
** Run Access in Windows 7/XP compatibility mode?
 
* https://stackoverflow.com/questions/54717598/system-resource-exceeded-when-appending-a-query-access-2016
** Big queries?
*** Had heard about update queries in general being more likely to cause this error; ours isn't an update on. It is large-ish, but as small as it can be. When the query runs successfully it finishes in ~5 seconds, so I kind of doubt the size is the issue.
 
* https://accessexperts.com/blog/2018/04/22/getting-system-resource-exceeded-messages-heres-one-way-to-fix-it/
** Windows 10?
== Steps Sending Locally ==
* Open ccmdb.data.mdb
* Go do Database Tools
* Visual basic
* Click Global -You will see txt message
** ' Public Const Centralized_data_accdb = Outpath & "Centralized_data.accdb" –become green once you add apostrophe (')
** Public Const Centralized_data_accdb = "C:\Centralized_CCMDB\" & "Centralized_data.accdb"
* Then save
 
== Sending ==
 
** Before you send make sure you have the newest version of ccmdb.data.mdb and you are in Pull of CFE
** Copy the latest backup you want to send
** Click Send

Revision as of 18:39, 2020 May 5

Template:Fixed Bug

This problem was fixed with CCMDB.accdb_Change_Log_2019#2019-12-07.

See page history for details, deleting from here so they don't pollute the searches.