locked
SQL SErver 2008 Locking Issue RRS feed

  • Question

  • Hey guys,

    I was hoping someone might be able to help me out with an issue that just started to raise its little head in the last few weeks.

    Just so you know, I'm not a DB admin at all, I just get lumped with sorting these issues out for my company, so please bear with me if I ask stupid questions.

    For the last 8weeks, I've been getting the following 5 errors in a 2min timeline around same time each Saturday afternoon.

    "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."

    I get 4 of those and then the following error

    "Replication-Replication Transaction-Log Reader Subsystem: agent RETAIL_SQL_SVR-Retail-3 failed. The process could not execute 'sp_MSadd_replcmds' on 'RETAIL_SQL_SVR'."

     

    Usually they don't cause any issue that I can find, except twice it has killed the replication snapshot of the DB to our DW server. Which causes me to have to make a new snapshot to fix the issue.

     

    Now, at the time the errors occur, I can only find 1 job that runs, its a multiple Archive job on various databases. So, I'm getting the programmers to have a look at their sp to make sure there not causing any issues.

     

    But in case its something else, is there some form of extensive logging I can turn on that will show everything that happening on the SQL instance at that time?

     

    Cheers

     



    Sunday, March 20, 2011 9:49 PM

Answers

  • Hi,

    From the error message, it seems that you got a 1204 error message – unable to lock resource due to memory starvation. What is your locks setting in the server configuration options (SELECT value FROM sys.configurations where name = 'locks';)? The default setting is 0, which allows the Database Engine to manage locks by lock pool dynamically. However, the lock pool won’t acquire more than 60% memory allocated to the Database Engine. If lock pool has reached 60% of the memory acquired by the amount of the Database Engine, or no more memory is available on the computer, further request for locks will generate an error. More information can be found in Books Online: http://msdn.microsoft.com/en-us/library/ms175978.aspx.

     

    If you are using default locks server configure setting, which is dynamic. The issue you encounter may be due to that: 1) Too many locks have been acquired but not released which exceeds the limitation of lock resources (memory); 2) The server memory may be not well configured such as setting max server to low therefore no more lock can be acquired.

    1) please run the following query to get how many locks are acquired or requested by each of databases:

    SELECT
    	DB_NAME(l.resource_database_id), 
    	l.request_status, 
    	COUNT(*) as [Lock_Number]
    FROM sys.dm_tran_locks as l
    where l.request_session_id>50 
    group by l.resource_database_id,l.request_status
    order by [Lock_Number]
    

    If there is any session blocked by other sessions, you could run the following query to get blocking sessions:

    SELECT
    	l.request_session_id,
    	DB_NAME(l.resource_database_id),
    	l.resource_type,
    	l.resource_subtype,
    	l.resource_description,
    	l.resource_associated_entity_id,
    	l.request_mode,
    	l.request_status,
    	t.blocking_session_id,
    	t.resource_description
    FROM sys.dm_tran_locks AS l LEFT JOIN sys.dm_os_waiting_tasks AS t
    	ON l.lock_owner_address = t.resource_address
    WHERE l.request_session_id>50 
    	AND l.request_session_id <> @@SPID
    ORDER BY l.request_session_id DESC
    

    Please note that transaction isolation level and lock escalation settings can affect lock manage to acquire or release locks. The following KB articles are about how to minitor and resolve blocking problems, see: http://support.microsoft.com/kb/224453 and http://support.microsoft.com/kb/271509.

    2) What are your max and min memory settings for server instanace and how much physical memory you have. Please also monitor Total Server Memory (KB) and Target Server Memory (KB) performance accounts to see if you have any memory pressure issues. If you do have memory perssure issues, you may need to tune your queries and add configure SQL Server to use more memory as well.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by WeiLin Qiao Monday, March 28, 2011 10:09 AM
    Tuesday, March 22, 2011 8:18 AM

All replies

  • The first error you posted sounds like a memory issue to me.

    What's the version of SQL Server?

    How much memory do you have on the server?

    Have you allocated MAX memory to SQL Server? If yes, how much?

    Can you provide more details about the job which you anticipate as the convict?

    Monday, March 21, 2011 2:10 AM
  • But in case its something else, is there some form of extensive logging I can turn on that will show everything that happening on the SQL instance at that time?

    You betcha!

    Profiler

    http://msdn.microsoft.com/en-us/library/ms181091.aspx

    It can produce way too much detail, so learn to use the filters, and maybe set it up as an SP not using the online tool.  Hopefully some of your techs are already familiar with it.

    --

    Other than that, I'm not sure what your issue could be, if you say there's only one job (and no users?) online when this happens.  One possibility is that some app is starting a transaction and leaving it open, slowly locking up everything even when there's no current activity.

    Just run sp_who2 and look for "blocked by" values.

    Good luck!

    Josh

     

    Monday, March 21, 2011 4:36 AM
  • Hi,

    From the error message, it seems that you got a 1204 error message – unable to lock resource due to memory starvation. What is your locks setting in the server configuration options (SELECT value FROM sys.configurations where name = 'locks';)? The default setting is 0, which allows the Database Engine to manage locks by lock pool dynamically. However, the lock pool won’t acquire more than 60% memory allocated to the Database Engine. If lock pool has reached 60% of the memory acquired by the amount of the Database Engine, or no more memory is available on the computer, further request for locks will generate an error. More information can be found in Books Online: http://msdn.microsoft.com/en-us/library/ms175978.aspx.

     

    If you are using default locks server configure setting, which is dynamic. The issue you encounter may be due to that: 1) Too many locks have been acquired but not released which exceeds the limitation of lock resources (memory); 2) The server memory may be not well configured such as setting max server to low therefore no more lock can be acquired.

    1) please run the following query to get how many locks are acquired or requested by each of databases:

    SELECT
    	DB_NAME(l.resource_database_id), 
    	l.request_status, 
    	COUNT(*) as [Lock_Number]
    FROM sys.dm_tran_locks as l
    where l.request_session_id>50 
    group by l.resource_database_id,l.request_status
    order by [Lock_Number]
    

    If there is any session blocked by other sessions, you could run the following query to get blocking sessions:

    SELECT
    	l.request_session_id,
    	DB_NAME(l.resource_database_id),
    	l.resource_type,
    	l.resource_subtype,
    	l.resource_description,
    	l.resource_associated_entity_id,
    	l.request_mode,
    	l.request_status,
    	t.blocking_session_id,
    	t.resource_description
    FROM sys.dm_tran_locks AS l LEFT JOIN sys.dm_os_waiting_tasks AS t
    	ON l.lock_owner_address = t.resource_address
    WHERE l.request_session_id>50 
    	AND l.request_session_id <> @@SPID
    ORDER BY l.request_session_id DESC
    

    Please note that transaction isolation level and lock escalation settings can affect lock manage to acquire or release locks. The following KB articles are about how to minitor and resolve blocking problems, see: http://support.microsoft.com/kb/224453 and http://support.microsoft.com/kb/271509.

    2) What are your max and min memory settings for server instanace and how much physical memory you have. Please also monitor Total Server Memory (KB) and Target Server Memory (KB) performance accounts to see if you have any memory pressure issues. If you do have memory perssure issues, you may need to tune your queries and add configure SQL Server to use more memory as well.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by WeiLin Qiao Monday, March 28, 2011 10:09 AM
    Tuesday, March 22, 2011 8:18 AM