System resource exceeded: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
Line 20: Line 20:
**Pagasa and I had a look at STB_MEDD (Debbie’s) data, and it 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.  
**Pagasa and I had a look at STB_MEDD (Debbie’s) data, and it 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.  


In the meantime, and at least until Trish and Julie are back and have cleared their vacation backlog and have a chance to discuss, the following will have to be our '''work around''':  
In the meantime, the following will have to be our '''work around''':  
*If you have this error, stop trying to send, this error will keep coming up.  
*If you have this error, stop trying to send, this error will keep coming up.  
*Once a week, Data Collector must coordinate with [[p:Pagasa Torres | Pagasa]] for her to send your completed files. You will need to work very closely together on this so we don’t lose records. Here is how it will work:  
*Once a week, Data Collector must coordinate with [[p:Pagasa Torres | Pagasa]] for her to send your completed files. You will need to work very closely together on this so we don’t lose records. Here is how it will work:  
**Data Collector:  must 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
**Data Collector:  must 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
**Collector must run [[Pre-send Checker]] and correct all error ID'd before you do [[News and backup]] each day.
**Pagasa and Data Collector: establish a phone call between the two of you
**Pagasa and Data Collector: establish a phone call between the two of you
**Data Collector: close out of Access and do a news and backup and leave Access closed
**Data Collector: close out of Access and do a news and backup and leave Access closed

Revision as of 12:03, 2019 November 26


The STB_MedB laptop gets the following error message when sending:

Microsoft Access - System resource exceeded

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.

  • main office needs documentation here in order to get better idea which laptop, how often and perhaps this would help us ID a pattern.Trish Ostryzniuk 16:48, 2019 October 1 (CDT)
  • SMW


  • Cargo


  • Categories

Interim work-around to have Pagasa send for a collector who gets this error

currently being done by:

  • HSC_H4_h - started Nov 12.19
  • HSC_A4-started Nov 7.19
  • STB_MEDB - started Nov 5.19
  • STB_MedD - started Sept 2019
  • GRA_N3
    • Pagasa and I had a look at STB_MEDD (Debbie’s) data, and it 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.

In the meantime, the following will have to be our work around:

  • If you have this error, stop trying to send, this error will keep coming up.
  • Once a week, Data Collector must coordinate with Pagasa for her to send your completed files. You will need to work very closely together on this so we don’t lose records. Here is how it will work:
    • Data Collector: must 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
    • Collector must run Pre-send Checker and correct all error ID'd before you do News and backup each day.
    • Pagasa and Data Collector: establish a phone call between the two of you
    • Data Collector: close out of Access and do a news and backup and leave Access closed
    • Pagasa: download the newest backup file, delete the incompletes, and send the completes (3-5min)
    • If all goes well, Pagasa will tell Data Collector to open Access and delete the completes
    • Data Collector: you would need to delete the completes because otherwise it will be impossible otherwise to distinguish between the completes that Pagasa sent, and the completes she has not yet sent
    • Data Collector: do one more News and Backup to move that to the newest status

This should allow files to keep being offloaded from Debbie’s laptop slowly, but it causes extra work for Debbie and Pagasa, so this is not a long term solution. We will need to discuss a longer term solution when Trish and Julie are back.

Observation - A new build of Windows10 was deployed at around the same time this became more common again

Herman, do we have an idea which versions would be the "working" vs "broken" ones? 
  • SMW


  • Cargo


  • Categories

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)

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

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

Noticed that we may not be getting Office updates installed (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

emailed Brandon to see if this might be possible to test.

  • SMW


  • Cargo


  • Categories

Related articles

Related articles:

Considerations