On 6.5 the table lock escalation threshold has to be configured manually. See LE threshold maximum, LE threshold minimum, LE threshold percent. You might also want to increase LOCKS, default 5000 isn't enough... just remember it costs 32 bytes per. You could also do a single table backup and restore (removed in 7.0), but that shouldn't really be necessary, in your case.
But if I have Locks set to 5000 and LE threshold maximum set to 200 , I thought the table lock escalation would happen before the number of locks reached 5000. Do you know why this isn't the case ?
Crito <cr...@nospam.net> wrote in message news:#$9WKSDQ$GA.77@cppssbbsa05... > On 6.5 the table lock escalation threshold has to be configured manually. > See LE threshold maximum, LE threshold minimum, LE threshold percent. You > might also want to increase LOCKS, default 5000 isn't enough... just > remember it costs 32 bytes per. You could also do a single table backup and > restore (removed in 7.0), but that shouldn't really be necessary, in your > case.
Extent locks do not escalate to a table lock. The escalation routines only look at the number of page locks you have. What is locks set at? Can you increase it?
You could also try forcing a table lock on the NewTable before you do the insert... something like this:
begin tran select count(some_column_in_newtable_with_no_index) from newtable (tablockx)
The extent locks are probably new extents being allocated - since SQL Server needs the new extent it needs to lock it so that some other process doing inserts doesn't try and also write to it.
-- Tony Rogerson SQL Server MVP Torver Computer Consultants Ltd.
