SQL Server Database Engine Locks

Owned by Knowledge Assistant
Last updated: Jul 26, 2023 by Moriah Yorkey
2 min read
Description of Issue

Munis database conversion failed during engagement. How do I resolve SQL transaction locks during a Munis upgrade?

Context

  • E-ERP

  • Munis

  • v2019.1.22+

  • Tyler Deploy

  • Databases

  • Performance

  • Transactions

  • Locks

Cause

During the Munis ERP deployment, an alter failure indicating, "The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users..." is returned. This error typically arises due to resource constraints on the source server.

Resolution

  1. Increase the RAM on the source server(s).

    1. SaaS: submit an infrastructure request to increase the virtual machine's performance.

    2. On-Premises: increase RAM on the impacted server(s).

  2. If not already done so as a part of the RAM increase, reboot the server.

    1. This is necessary as it implements the changes that were made.

  3. On the server that hosts the Munis database, increase the Maximum server memory (in MB) value within SQL Server Management Studio (SSMS).

    1. Within your search bar, search and open SSMS.

    2. When prompted, input the Server name.

      1. Click Connect.

  4. Once loaded, right-click on the server instance in SSMS.

    1. From the dropdown, select Properties.

    2. A page displaying server properties will now appear. From the list of options below Select a page, click Memory.

    3. In the area marked as Maximum server memory (in MB), change the value to 24576.

      1. Click OK.

  5. Attempt another run at your Munis ERP deployment.

Additional Information

  • It is recommend that, for clients with larger Munis databases, the database server's RAM be increased to 48 or 64 gigabytes as necessary. Start with smaller increases and allocate more as needed.

  • The number of processing cores on the database server should be taken into consideration when increasing RAM.

    • Example #1: servers with 48GB of RAM should get 4 cores.

    • Example #2: servers with 64GB of RAM should get 6 cores.

  • Increasing the SQL Server Management Studio (SSMS) instance's maximum server memory to 24 or 49 gigabytes–depending on the size of the client's database–increases query performance substantially.

    • The Maximum server memory (in MB) field only accepts values in megabytes:

      • For clients with 48GB of RAM, you may set memory utilization at: 24576MB.

      • For clients with 64GB of RAM, you may set memory utilization at: 49152MB.

  • Clients that are heavy in Accounts Receivable (AR), Utility Billing (UB), and Work Orders (WO) typically have longer alter times, as these tables tend to grow exponentially.

    • In these situations, it is necessary to increase the maximum server memory to accommodate the database's growth.

    • The threshold at which you set the Maximum server memory (in MB) field is completely dependent on the amount of data within the Munis database.

  •  You should also plan under the assumption that a client's database could double in size.

    • You will need to increase disk space for both the Munis and tempDB drives, in the event this does occur. 

    • Example: a client with a 385GB database has the potential to convert to a 770GB database.

Â