Can't delete row

Posted on 2011-10-11
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
    LVL 32

    Accepted Solution

    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 39

    Expert Comment

    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 39

    Assisted Solution

    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
    LVL 1

    Author Comment

    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.
    LVL 1

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now