Disable SQL Server Table Lock Escalation

Invantive Data Replicator uses massive parallel processing to improve throughput. In several scenarios a single database table can be accessed by multiple parallel processes at the same time, reading and writing data. Invantive Data Replicator ensures that the access is logical correct.

However, SQL Server has inherited the concept of ‘lock escalation’ of table from Sybase. Depending on your set up, SQL Server timeouts can occur when the table lock method is not configured correctly. Such a timeout takes one hour to appear after starting the replication process with the default configuration of Invantive Data Replicator.

This article explains how to disable lock escalation on SQL Server. Please consult your DBA or hire a SQL Server specialist before making changes when you consider yourself insufficiently skilled to overlook the consequences of disabling lock escalation.

Instructions

Execute the following steps:

  1. Log on to the database as owner of the Invantive Data Replicator repository.
  2. Run the query listed below.
  3. Execute the replication process again.
SELECT 'alter table [' + t.name + '] set (lock_escalation=disable)' stmt
from   sys.tables t 
where  t.lock_escalation_desc = 'TABLE' 
and    t.schema_id = schema_id()
order
by     stmt