tlgtech
asked on
MS Acess 2010 3035 Error With Multiple Users on Windows 2008 R2 Terminal Server
I have two applications used by one company. Both systems are MS Access 2010 Accde front ends to MS Access Accdb data files. I recently upgraded both systems from Access 2003 running in Windows 2003 because the old Windows 2003 servers were dying. We run two terminal servers to one file server. We are running Symantec Endpoint Protection. Both systems are running Record Level Locking
The system on startup for each user will make a new copy of the accde file from the source library to their working directory and the old file is deleted. I have found that it is faster just to swap out the files than to compact the original file on close each time.
System one, Order Entry is used to create orders. Each user may open two copies of Order Entry, one for Company A and a second for Company B. System One has up to 11 users. So we can have up to 22 versions of MS Access 2010 running on the two Win2008R2 TS. These servers have plenty of resources and we barely even tax them.
System two is the warehouse. We have 7 versions of MS Acceess running the Warehouse software.
The data files are broke down into about 10 different accdb files on a common data shared drive on the file server.
We are running MS Access 2010 32 bit runtime (WITHOUT SP1) on the Terminal Servers.
PROBLEM: after upgrading to 2010 and installing the hotfix to increase performance we have found that we are blowing 3035 Resource errors. We have adjusted the MaxLocks and Buffer and currently have each at 350,000 and 50,000. Searches are fast 10 times in a row and then we will pop a 3035 doing the exact same thing. Invoice posting fails every time. Payment posting fails.
This afternoon, no one was working at the site, I logged onto the Terminal Server in a user session and ran EVERYTHING without blowing any errors! It is as if the conflicts on the data files caused by multiple users are causing the 3035 error.
We have contacted Microsoft and have a ticket open with them. They suggested the MaxLocks and Buffer settings to find a sweet spot. They also suggested running in compatibility mode. We have tried Win7, Win2003, WinXP Sp3. None made any difference.
We do have one Windows & 32 bit PC hooked up to the network that has MS Access 2010 installed on it. We do not blow any errors with this PC using the same files in the same location on the file server.
Any guidance as to what is going on here will be greatly appreciated. We have lots of time in on this project and it seems as if we are spinning our wheels.
Thanks in advance for any advice.
The system on startup for each user will make a new copy of the accde file from the source library to their working directory and the old file is deleted. I have found that it is faster just to swap out the files than to compact the original file on close each time.
System one, Order Entry is used to create orders. Each user may open two copies of Order Entry, one for Company A and a second for Company B. System One has up to 11 users. So we can have up to 22 versions of MS Access 2010 running on the two Win2008R2 TS. These servers have plenty of resources and we barely even tax them.
System two is the warehouse. We have 7 versions of MS Acceess running the Warehouse software.
The data files are broke down into about 10 different accdb files on a common data shared drive on the file server.
We are running MS Access 2010 32 bit runtime (WITHOUT SP1) on the Terminal Servers.
PROBLEM: after upgrading to 2010 and installing the hotfix to increase performance we have found that we are blowing 3035 Resource errors. We have adjusted the MaxLocks and Buffer and currently have each at 350,000 and 50,000. Searches are fast 10 times in a row and then we will pop a 3035 doing the exact same thing. Invoice posting fails every time. Payment posting fails.
This afternoon, no one was working at the site, I logged onto the Terminal Server in a user session and ran EVERYTHING without blowing any errors! It is as if the conflicts on the data files caused by multiple users are causing the 3035 error.
We have contacted Microsoft and have a ticket open with them. They suggested the MaxLocks and Buffer settings to find a sweet spot. They also suggested running in compatibility mode. We have tried Win7, Win2003, WinXP Sp3. None made any difference.
We do have one Windows & 32 bit PC hooked up to the network that has MS Access 2010 installed on it. We do not blow any errors with this PC using the same files in the same location on the file server.
Any guidance as to what is going on here will be greatly appreciated. We have lots of time in on this project and it seems as if we are spinning our wheels.
Thanks in advance for any advice.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If both FE and BE will be placed on one server (TS) you will eliminate possible network problems.
ASKER
To provide accuracy, we were actually getting the error message from a Windows 7 64-bit workstation running the full version of Access 2010 as well as both of the new 2008 R2 RDS servers running Access 2010 RT. It was rare but we were getting it. In the original post I said that we were not.
On 4/13 we upgraded Access 2003 RT to Access 2010 RT on one of the original 2003 Terminal Servers. While testing, this server received no errors but our final "solution" was to move the BE from the original 2003 Server to a 2008 R2 server. Since moving the BE to 2008 R2 we are no longer getting the 3035 System Resources Exceeded errors on any server or workstation.
In the end we don't know why the database for system one was having issues while the database for system two always worked. System one is larger and was originally developed in Access 97 or earlier and has had multiple developers and upgrades over the years so maybe that is a possible reason. System two was developed by us in Access 2003 and is smaller. Microsoft has no explanation.
For the sake of anyone else who may run into this, here is a list (not in order) of most of what we tried.
1. Adjusted MaxLocksPerFile (450000) and MaxBuffer(50000) registry settings. This improved performance and reduced the number of errors but did not completely remove them.
2. Disabled Symantec EndPoint.
3. Moved the FE to the local hard drive of one of the the RDS servers.
4. Tried various compatibility mode settings.
5. Created a new shell for the database and pulled everything into it.
6. Reworked the code in the problem areas (reports) to remove possible memory leaks as suggested by Microsoft support. (set object variables to nothing)
7. Reinstalled Access 2010 RT.
8. Disabled IPv6.
9. Disabled TCP/IP auto-tuning.
10. Upgraded one of the original 2003 Terminal Servers to Access 2010 RT. Received no errors.
11. Moved the BE to a new 2008 R2 server. Received no errors.
On 4/13 we upgraded Access 2003 RT to Access 2010 RT on one of the original 2003 Terminal Servers. While testing, this server received no errors but our final "solution" was to move the BE from the original 2003 Server to a 2008 R2 server. Since moving the BE to 2008 R2 we are no longer getting the 3035 System Resources Exceeded errors on any server or workstation.
In the end we don't know why the database for system one was having issues while the database for system two always worked. System one is larger and was originally developed in Access 97 or earlier and has had multiple developers and upgrades over the years so maybe that is a possible reason. System two was developed by us in Access 2003 and is smaller. Microsoft has no explanation.
For the sake of anyone else who may run into this, here is a list (not in order) of most of what we tried.
1. Adjusted MaxLocksPerFile (450000) and MaxBuffer(50000) registry settings. This improved performance and reduced the number of errors but did not completely remove them.
2. Disabled Symantec EndPoint.
3. Moved the FE to the local hard drive of one of the the RDS servers.
4. Tried various compatibility mode settings.
5. Created a new shell for the database and pulled everything into it.
6. Reworked the code in the problem areas (reports) to remove possible memory leaks as suggested by Microsoft support. (set object variables to nothing)
7. Reinstalled Access 2010 RT.
8. Disabled IPv6.
9. Disabled TCP/IP auto-tuning.
10. Upgraded one of the original 2003 Terminal Servers to Access 2010 RT. Received no errors.
11. Moved the BE to a new 2008 R2 server. Received no errors.
ASKER
We had multiple BE files on a Windows 2003 file server. The front ends are on a new TS running Windows 2008R2. For some reason, moving the data files to another Windows 2008 R2 server did the trick. The application is very fast, much fastser than when data and FE were on Windows 2003. We did open a Microsoft ticket and moving the data files was one of their suggestions also.
So moving BE to Windows 2008 R2 fixed our problem. But it does not explain why the warehouse system ran great with the FE on Windows 2008 R2 and the BE on Windows 2003. Both systems use the Customer and Invoice data files so you would think both would be equally impacted.
Good news is client is happy!
Thanks als315 for your suggestion
So moving BE to Windows 2008 R2 fixed our problem. But it does not explain why the warehouse system ran great with the FE on Windows 2008 R2 and the BE on Windows 2003. Both systems use the Customer and Invoice data files so you would think both would be equally impacted.
Good news is client is happy!
Thanks als315 for your suggestion
ASKER
Added information:
Main accdb file that everyone reads from and writes to is about 480 Mb.
Order Entry has a few other accdb files they write to (log files, error files, etc)
Warehouse users have several other accdb files they use.
All the other files range in size from 1 Mb to 100 Mb for the log files.
The front end accde files are copied FROM the file server source library to a directory for that user for the company being run which is again on the file server. So both FE and BE are on the FS which I believe is also Windows 2003 although I could be mistaken on that one.
Currently we are running all users on ONE TS and speed is good. So it is conceivable that we could put the data files on the disk of a TS. I just need to know what that might solve or prove.