• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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!
0
jmarkfoley
Asked:
jmarkfoley
  • 2
  • 2
2 Solutions
 
Brendt HessSenior DBACommented:
There are several possibilities that could apply here.  Let's look at a few of them.  If these do not work, then we may have to look at other possibilities.  Note that I am assuming that the same type of query has been run by you before, and that you know what a normal duration is for a delete on this table.

Start by taking a backup of the database, if possible.  If you can, and if there is sufficient free space, restore the database and try the command on the restored database.  If it works quickly, fine; something has part of the table locked in such a way that you can not make your needed change - OR, there are on delete cascade Foreign Keys set up referencing this table, and one or more of those tables are blocking.  Time to look at locks in the table - which is another series of posts entirely.  

Noting blocking - if you look at the process running the delete in activity monitor, what is the status of the process?  Is it blocked (and, if so, find the blocking process), or is it waiting on a specific resource, or ??.  Knowing the state of the delete will allow for better handling of this issue.

Another thing that could be blocking is an unclosed transaction.  Again, there could be a whole post on identifying and correcting hanging transactions, but if you think this is what it is, it should be partially reflected in the activity monitor.  (*NOTE:  Realizing that not everyone has used the activity monitor, it is found in SQL Server Management Studio - expand your server's folder, expand Management, and select Activity Monitor, which should be the first item beneath the folders nested under Management).

More detail is needed before the diagnosis can be continued.
0
 
lcohanDatabase AnalystCommented:
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.
0
 
lcohanDatabase AnalystCommented:
You can also run a DBCC CHECKTABLE command if the above don't help like in the example below:

DBCC CHECKTABLE (table_name) WITH ALL_ERRORMSGS;


This can be stopped/resumed at anytime if its taking too much resources
0
 
jmarkfoleyAuthor Commented:
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.
0
 
jmarkfoleyAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now