asked on

Can't delete row

I am trying to do:
delete from tblPaEmpBenefits where recId = 6893

where recId exists and is the primary key. When I run this, the query hangs forever (and by forever I mean more than an hour). I don't know what's going on. I can select, but I can't update either.

This just happened this morning. Something must have happened this morning to lock this up. How do I figure out what's wrong? Help!

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

Are there any Foreign Keys on that table linked to the PK? And is the PK clustered on IDENTITY column (I guess it's worse case scenario)

For instance that row could be a parent "ProjectID" tied to millions of entries in child tables linked through FKeys that (worse case) have no indexes on the FKey columns. That's what hapens usualy in a RDBMS when you try to delete a parent record tied to too many children.

SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark

ASKER

OK, I found the problem. There was another view using the tblPaEmpBenefits table that has something wrong with it and was simply running forever. This query was running on another workstation, so the query I was running was hung while the resource was locked up. The other bad view didn't have anything really obvious wrong with it other than it was a view using other view and itself in the same view as a join, so maybe just too confusing for SQL Server. I rewrote that query eliminating some of the complexity and recursion and everything seems to work fine.

Mark

ASKER

Thanks for all input. While no answer exactly corresponded, bhess1's suggestion on looking for blocking processes pointed me in the right direction. thanks all.