Posted by Erland Sommarskog on 06/14/05 01:05

pb648174 (google@webpaul.net) writes:
> This is interesting - adding a clustered index on the identity column
> fixed the previous problem, so I picked one module (which has about 5
> or 6 tables) to see if I could free up that module while the copy
> process was running. I went and put clustered indexes on all the
> identity columns of those tables, but now another table has a locking
> problem, although this time, not on the index. The output from that
> line from sp_lock is below:
>
> 54 8 2101582525 1 PAG 1:26568 S WAIT
>
> So now it is a page lock, which from what I've read is a group of
> records. That seems resonable given that I am copying large amounts of
> data, but why is it stopping my other process?

Lacking telepathic abilities, I can't say why.

What you could try is to use aba_lockinfo, which you find on my web
site, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This procedure
give you an overview active process, which object they lock, and also
current statements. This gives a little better idea of what is going
on.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]