SQL Delete (Suspended in activity monitor)

Question

I have a table containing hundreds of entries and I am trying to delete a small range. It is taking a long time, in fact it is not being executed.

I monitored the query from the activity monitor and its status is "Suspended"

Is there anyone knows what may cause this problem?

Answer 1

What's the SQL you're running? Any triggers on the table, or any cascaded deletes to tables that have triggers on? Anything in the "Blocked By" column or Wait Type?

Try executing the following in management studio (on the database in question):

dbcc checkdb
dbcc checkcatalog

Answer 2

Depending on the size of your database and the number of constraints that have to be checked when deleting a row, deletes can take a very, very long time. I never delete more than one row at a time from my databases. It's just too resource-intensive on the system. This is one of the few places where a cursor, or an iteration in non-database code (C#, Java, whatever) can beat a set-based query. I even put a small delay of a few milliseconds between deletes to make sure nothing gets locked up for too long. You can bring a database to its knees by deleting a range of data from a table.