search cancel

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time When deleting a client

book

Article ID: 202968

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

Below Error seen while trying to delete a client:

 

 U00045015 The previous error was caused by 'com.microsoft.sqlserver.jdbc.SQLServ

erException: "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. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions."' at 'com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError():216'.

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Cause

This issue was caused by a SQL Server configuration setting. The default value for 'Locks' was changed from 0 to something else (in this case to 5000). By limiting how many 'Locks' are allowed, it caused SQL Server to fail to provide the necessary Lock resources during our updates in stored procedures and other tables.

Resolution

Resolution
Check the following setting and make the appropriate changes if needed:

1. Open Microsoft SQL Server Management Studio
2. Right-Click on your SQL instance and click on Properties
3. Under the Advanced page, look under the Parallelism section for 'Locks'
4. By default the 'Locks' value is 0. If it is something different, make the change back to 0
Note: If for some reason the UI doesn't take the change, run the following query to set the value to 0 again:

exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'Locks', 0;
reconfigure;

5. Restart the SQL Services