Can't delete row

Posted on 2011-10-11
Medium Priority
Last Modified: 2013-11-05
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!
Question by:jmarkfoley
  • 2
  • 2
LVL 32

Accepted Solution

Brendt Hess earned 1600 total points
ID: 36950033
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.
LVL 40

Expert Comment

ID: 36951182
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.
LVL 40

Assisted Solution

lcohan earned 400 total points
ID: 36951201
You can also run a DBCC CHECKTABLE command if the above don't help like in the example below:


This can be stopped/resumed at anytime if its taking too much resources

Author Comment

ID: 36979999
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.

Author Closing Comment

ID: 36980017
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.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question