CODEWITHSUNDEEP
sqlsql-server
Joel Priddy
Joel Priddy

Reputation: 441

SQL Server Single Record Delete Not Complete in 3+ minutes

I have a table with 810 records in SQL Server 2005 that looks like this:

When I try to run the following query:

DELETE FROM Attachments
WHERE UniqueId = 1122 AND FileId = 923

it runs for 3+ minutes and doesn't complete. The table is really small, the file size is 4900 bytes, and I'm only trying to delete a single record. Anyone have any ideas of what could be slowing this thing to a crawl?

EDIT: I very much appreciate the time each of you took to try to answer my question. I would +1 each of you, but I can't do that yet. We did finally find the answer...we had to rebuild the indexes. The rebuild took much longer than we expected (10minutes or so), but once we did it the table resumed functioning as normal. Evidently someone turned off the maintenance for that database and forgot to turn it back on. Hopefully, someone else can benefit from our misfortune.

Upvotes: 0

Views: 74

Answers (2)

HLGEM
HLGEM

Reputation: 96542

Sure, you could have a trigger that is doing other work. You could have blocking from another process. Or you could have cacade delete turned on and instead of deleting one record you are delteing millions. Or you could have hundreds of FKs that have to be checked.

But check for blocking first!

Upvotes: 1

Can Yener
Can Yener

Reputation: 189

Maybe someone else is doing an insert or update operation which locks the table you work with.

Upvotes: 0

Related Questions