Hi

Ive got piece of sql that selects a list fo client ids from a table using a cursor and then deletes data from a a series of tables based on that id.. see cut down version below.. anyways it takes an age to run and on running an sp_who2 I see its blocking itself.

Is there anyway i.e query plan or a trace that would show up exaclty where its blocking itself ?
And is there anyway round it ?

thanks si


<sub>declare @id varchar (10)

declare id_cur cursor for
        select id
        from redundantdata

open id_cur

fetch next from id_cur into @id

while @@fetch_status = 0
begin
  
delete USER_INSTALL  where id = @id

delete USER_JOB  where id = @id

delete USER_log where id = @id


        fetch next from id_cur into @id
end

close id_cur
deallocate id_cur
go</sub>

Make sure you have correct indexes on the tables you are deleting...

Why is a SPID blocking itself in sysprocesses? How is it possible?

thank you .. really appreciated :-)

best wishes

simon