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