none
Any workaround for not enough resources for LOCKS?

    Question

  • Hi,

    I have 12GB table with 46M records in it. My server has 16GB of memory. Im using Merge replications with dinamic filtering. Whenever replications try to replicate this table I get:

    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. (Source: MSSQLServer, Error number: 1204)

    "SELECT COUNT(*) FROM sys.dm_tran_locks" returns around 8,5M LOCKS at the point where the replication fails.

    I assume that 16GB is not enough for this operation, which I could understand... However is there a workaround on how I could replicate in smaller batches by manipulating some replication parameters, or any other way? I really need to replicate this table without upgrading RAM.

    Sunday, April 28, 2013 10:26 AM

All replies

  • Hi,

    Did you check if the 8.5M locks are mostly row locks?

    If so, maybe disabling row locks can help (of course it has its drawbacks).


    My Blog

    Monday, April 29, 2013 7:38 AM
  • Hi, can you give more details on how to check if it is row locks and how to disable it?
    Monday, April 29, 2013 11:23 AM
  • I like to use this script:

    if object_id('tempdb..#locks') is not null
    	drop table #locks
    go
    create table #locks(spid int,dbid int,objid int,indid int,type varchar(100),resource varchar(100),mode varchar(100),status varchar(100),objectName varchar(512))
    go
    
    insert #locks (spid,dbid,objid,indid,type,resource,mode,status) exec sp_lock 
    exec sp_msforeachdb 'update l
    					 set objectName = o.name
    					 from #locks l inner join ?.sys.objects o with (nolock)
    					 on l.objid = o.object_id and l.dbid = db_id(''?'')					 '
    
    select spid,db_name(dbid) as DBName,objid,objectName,indid,type,resource,mode,status from #locks

    You can filter according to objectName (and according to the other columns of course).

    You can also look at sys.dm_tran_locks.

    For disallowing row locks, see http://msdn.microsoft.com/en-us/library/ms186253.aspx


    My Blog

    Monday, April 29, 2013 12:15 PM
  • This warning has nothing to do with RAM.  It is telling you someone else has the row locked, so it cannot apply the change.

     

    Monday, April 29, 2013 1:33 PM