Performance issues and getting many 3218, 3164 or other record locking errors using Windows Terminal Server

Modified on: 2017-08-01 13:38:57 -0400

Design Manager Pro 7.0 uses the Microsoft Jet Database Engine version 4.0.  Jet is a very reliable database engine that supports up to 64 users with a much reduced implementation cost and operating cost over SQL server. If the issue described here are unacceptable than the only alternative would be upgrade to the SQL Server of Design Manager Pro. The Jet Engine comes tuned as a client-side data engine; this means that each client computer attached to the network is responsible for writing changes to the database.  In larger multi-user environments Design Manager recommends Terminal Services/RDS which turns Jet into a server side engine by bring all of the data processing onto the server.  Because the same instance of the Jet engine is being shared by all users on the Terminal Server some Jet registry setting adjustments need to be made in order for the record-locking and the data cache to operate efficiently. 

It is also recommended that the repair utility, which compacts the database, is run at least once a week or even every night after a reboot of the file server.  This can be done as part of a back-up plan and scheduled using the Windows Task Scheduler.  The program that should be run is Repair.exe found in the Design Manager directory.  Use the /quiet switch (i.e. repair.exe /quiet) – there is a space between .exe and /quiet - to prevent the utility from prompting the user.  In order for repair to run all users must be out of Design Manager, to guarantee this, a re-boot of the server can be scheduled prior to the scheduled repair.

Windows Update will automatically update the Jet Engine as it is free and comes with Windows.  For this reason, a repair must be run after each Windows update is installed in case Microsoft made any changes to the database format.  Repair will implement these changes.  If a repair is not run, GPF or Exception Fault errors can occur.   This is another reason why a scheduled repair is strongly recommended.

Below are the recommended changes that should be made to the Windows Registry for Jet on the Terminal Server Computer.  Please take all precautions, back-ups, etc. before editing the registry.  Note: An experienced technician may be needed to edit the registry as a mistake can cause the computer not to operate.  To ensure that the changes take effect, a re-boot of the server is recommended.  Please write down the default settings before making edits so you can return to those settings should problem arise.

Using regedit.exe, the settings are found under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

The settings should be changed as shown above, make sure you make them in Decimal.  The FlushTransactionTimeout should be set to 300, the LockDelay to 150, the MaxBufferSize to 8192 (the default is zero), and Threads to 9.  The MaxBufferSize should be reduced to 4096 when there are more than 15 or so concurrent users in Design Manager on the server.    For environments that require extremely heavy database access, try setting the FlushTransactionTimeout setting to 0.  This will cause background writes to occur and locks to be released immediately following the SharedAsyncDelay setting.

These settings are all about a balance between performance and immediate record availability.  For example, if you have a larger MaxBufferSize and a longer FlushTransactionTimeout setting then a large invoice may post faster because the system will cache the writes and allow the user to move on to other parts of the software.  However, if the user needs to immediately edit any of the records affected by the large invoice posting, then the records will not become available until the background writes complete, that is when the buffer fills up or the system becomes idle, which could be several minutes. The Jet Engine employs a simple cache and write schema, the cache is serviced by the server during idle times…if the database is in high demand and the occasional record locking error during busy times is unacceptable, then SQL Server would be a better alternative, but requires an additional server to be the SQL Server and requires the purchase of the SQL server software itself.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.