Lock manager out of space

Hi,

I'm getting an error which I'm pretty sure I can avoid for now, but I'd like to understand the underlying issue.

I'm have SQL CE 3.1 on Win XP and am replicating a reasonably large set of data with SQL Server 2005 using merge replication. The initial replication works fine, and brings the SQL CE database to around 800MB. Subsequent delta syncs are also fine. However, if I re-initialise the subscription, it chugs away for a while, grows the local database to just over 1Gb then errors with the following message:

The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.

I can avoid this issue by either deleteing the .sdf file and recreating the subscription from scratch, or by splitting the publication into smaller sets, and re-intialising each one seperately. Obviously SQL CE uses some sort of temporary or "lock" space to manage the re-initialisation. Is, as the error message suggests, there no way to increase this? How much space is there - ie what is the threshold overwhich I need to split up the update operations into multiple steps.

I'm assuming this is a SQL CE limitation, hence posting this here rather than under the replication forum.

Cheers

Looking at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

I can see the following setting:

default lock escalation-or-ssceefault lock escalation

The number of locks a transaction will acquire before attempting escalation from row to page, or from page to table. If not specified, the default value is 100.

It's a far shot, but maybe decreasing this number to 10 or lower will help you.

What does your connection string look like, anyway?

|||

Thanks for the suggestion. From our application we don't actually set this value, so I expect it's using the default 100. Our connection string is pretty minimal, and looks like this:

Data Source='filename.sdf';Max Database Size = 2048; Max Buffer Size = 1024;

I've tried testing different values in the default lock escalation setting using the subscription wizard in sql management studio which seems to enforce a minimum value of 50. It doesn't appear to make any difference to the result - I still get the lock manager running out of space.

|||

Received this information through a Device MVP and thought it might be useful for others:

"You hit this issue:

1) If a single transaction is dealing with more than 1 GB of pages.

2) If you are using v3.0 or v3.1

We have extended the limit in v3.5 (available in v3.5 Beta2), and you can now have a one bulk transaction which is updating more than 1 GB of pages."

Also this:

" The old limit is 2^18-1 lock references, now it is 2^32-1 lock references"

sql