April 17, 2013 at 5:54 pm
Hi,
Can someone help us to determine the resolution for the encountered error below.
Message
Executed as user: HO\sqlibg. 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. [SQLSTATE HY000] (Error 1204). The step failed.
Actions Taken:
1. Enabled AWE.
2. Input number of locks to 10K
3. Checked blocking sessions - none
April 17, 2013 at 9:40 pm
What the SQL Server agent job does? It could missing indexes if DELETE/UPDATE is being performed.
April 17, 2013 at 10:25 pm
PLease refer this Link to troubleshoot your issue :
April 18, 2013 at 12:36 am
You said that you took below actions.
Actions Taken:
1. Enabled AWE.
2. Input number of locks to 10K
3. Checked blocking sessions - none
What Processor architecture you are on? I mean 32 bit or 64 bit for OS and SQL?
Enabling AWE is fine on 32 bit systems, on 64 bit systems, it doesnt serve any value. Also, can you please advise what your job does? Locking occurs due to lot many reasons ranging from concurrency issues (Isolation Levels) to your design of calling SQL code (batch or SPs) in a long transaction etc etc...
Also, you seem to be less on memory allocation. Can you please give whats the total memory and whats allocated to SQL with any other setting like max and min memory, AWE etc?
Its good that there are no blocking sessions when you checked, but have you checked while job was running in and observed the blocking on server for some duration?
These are some things to start with but I am sure there can be more than this to be checked....
Thanks,
Vikas
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply