records wont delete

I have some records that will not delete, whenever I run a delete
statement in the Query analyzer, it never completes the statement, and
I am only deleting one record at a time. Can anyone tell me why a
record wouldn't delete?[posted and mailed, please reply in news]

alloowishus (alloowishus@.yahoo.com) writes:
> I have some records that will not delete, whenever I run a delete
> statement in the Query analyzer, it never completes the statement, and
> I am only deleting one record at a time. Can anyone tell me why a
> record wouldn't delete?

There are several possible reasons:

o The row you are trying to delete is locked by another process.
o There is a foreign-key constraint from a referencing table, and that
table is large, but the referencing column is not indexed, so
checking whether the row is deletable takes a very long time.
o A variation of the above, but the foreign-key relationship uses
cascading updates.
o The table has a DELETE trigger which takes a very long time to
execute.

To check for locking, issue an sp_who from another connection. If you see
a non-zero value in the Blk column, the process on that row is blocked by
the process in the Blk column.

To check the other, use sp_helpconstraint and sp_helptrigger to look
for constraints and triggers.

The above list is not necessarily exhaustive.

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

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