SQL Error 1204 - cannot obtain a LOCK resource at this time

17 May,2013 by Jack Vamvas

A DBA reported the error message “Error: 1204, Severity: 19, State: 4. 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.”

The message appeared during the daily SQL Server Error log report which checks for SQL Server Error log messages of interest to DBAs.

The SQL error 1204 message appears when memory thresholds are met.

SQL Server cannot obtain more memory from the Operating System. The memory is used by other resources or SQL Server has max server memory configured.

Lock manager doesn’t use more than 60% of memory available to SQL Server

 Possible Fixes

1) Release other applications using resources on the OS – use Task Manager  to identify other applications using the largest amount of memory.  Most DBAs attempt to maintain SQL Server on dedicated servers , but management software can creep in – such as Anti-Virus , monitoring agents and security analysis.

2) Increase the SQL Server max memory setting, if extra memory is available .

3) Use the sys.dm_tran_locks view to identify the session holding the largest amount of locks and kill the process. Before you kill the transaction record the details to

Read More

SQL Server – Find sql server Locks - SQL Server DBA

SQL Server - Use sys.dm_tran_locks to find requests waiting for an exclusive lock

SQL Server Performance ,memory pressure and memory usage


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Error 1204 - cannot obtain a LOCK resource at this time


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer