Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Posts
    10

    Unanswered: Can't delete single record? HELP!

    OK,

    This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? Here's the command I'm executing:

    DELETE FROM Invoices WHERE InvoiceID = 153345

    Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

    TIA
    Last edited by CSharper; 05-28-04 at 14:18.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Any triggers on the table?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Posts
    10
    Quote Originally Posted by blindman
    Any triggers on the table?
    No, no triggers on the table.

  4. #4
    Join Date
    May 2004
    Posts
    10
    Quote Originally Posted by shyguydavid
    Any triggles on this table?
    No triggles either

  5. #5
    Join Date
    May 2004
    Posts
    7
    what does the execution plan looks like for the delete statement?

    Are there foreign key constraints on Invoices table ?
    run something similar to the following:

    select object_name(constid) as FkeyName,
    object_name(fkeyid) as DependentTable from sysforeignkeys where rkeyid=object_id('Invoices')

    Is anything locking Invoices table when you try the delete?

    simas

  6. #6
    Join Date
    May 2004
    Posts
    10
    It won't even pull up an estimated execution plan. It just sits there doing nothing. There are several foreign keys, but I'm deleting from those tables prior to this delete, and they all delete without a hitch. It's just this table. I don't see anything locking the Invoices table.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Have you ran a DBCC CHECKDB to see if there are any integrity issues?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    use <Your_Database_Name>
    go
    dbcc opentran
    go

    You have to have an open transaction that references this table. Oh, and do KILL your DELETE before you issue the DBCC. Make sure that any rollback (doubt it) completes before you fire DBCC.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    May 2004
    Posts
    10
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'Accounting'.

    Nice thought though.

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    This is trying to pull a rabit out of the hat, but try to run UPDATE STATISTICS on the table. Also, try to drop and recreate the PRIMARY KEY index on that table. Then, see if you can delete the rows.

    Also, I don't know why in the world I didn't suggest this earlier. In Query Analyzer before you do anything else, run: SELECT @@SPID. Set up a profiler to capture all activity using that SPID as a filter. In particular, look for SQL:Batch Started; SQL:Batch Completed; all errors, all recompiles, all locks and blocks; RPCtarted; RPC:Completed. See if you can see anything in the profiler that would lead to this problem.

    Also, when this is running, if you run this in Query Analyzer do you get any results?

    SELECT * FROM master..sysprocesses WHERE blocked <> 0
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  11. #11
    Join Date
    May 2004
    Posts
    10
    UPDATE: DELETING THE PRIMARY KEY AND RECREATING IT WORKED. (no comment on how bad I think it is that this should ever have to be done, but it's fixed so I won't complain)

    Mad Thanks go out to those that helped me out with this!

    Special Thanks to Derrick!
    Last edited by CSharper; 06-01-04 at 14:45.

  12. #12
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    And you're sure you're on the latest service packs, critical updates, etc for Windows and SQL Server? Make sure the machine that has Query Analyzer on it has also been updated.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  13. #13
    Join Date
    May 2004
    Posts
    10
    SP3a on both, I'll have to check on the critical updates (since my boss thinks the internet is a fad). Thanks again for everything!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •