none
What can be causing deletions? RRS feed

  • Question

  • A user is reporting that some records get deleted mysteriously from the database. After doing all sorts of investigation, writing code (and adding tables) specifically to track what they are doing, I'm stumped. The records seem to be dissapearing without triggering any of the logging code (in the app front-end).

    I have evidence that the records once existed, in that they have print-outs from the system with the record ID's on them. These print-outs can only be printed if the records are commited (the record creating and printing are two seperate functions), so that has put a transaction roll-back out of the equation.

    I'm now looking at database relationships deleting them, like a cascading delete.

    My question is, is there an easy way to find out all of the relationships that could potentially cause a deletion of given record? I am hoping to avoid going through my database, table by table, to find this problem.

    Thanks.

    Wednesday, June 22, 2011 12:29 PM

Answers

  • You can query sys.foreign_keys view and check for delete_referal_action column. Please see if this is what you were looking for.

    Also there may be many things like triggers that may be causing this. I would suggest you to run SQL Profiler trace and capture the events that occurs. 


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

    • Proposed as answer by Naomi NModerator Wednesday, June 22, 2011 6:33 PM
    • Marked as answer by fweeee Wednesday, June 22, 2011 10:17 PM
    Wednesday, June 22, 2011 12:36 PM

All replies

  • 1. Cascading Deletes

    2. Triggers

    3. Manual Deletes

    4. SSIS jobs

    Adam


    Ctrl+Z
    Wednesday, June 22, 2011 12:35 PM
  • DELETE command or TRIGGER on that table for delete causes deletion, turn on SQL Server Profiler and capture all statements run against the database. Or you can define a trigger to log the info when delete is faired. What version you are using?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 22, 2011 12:35 PM
    Answerer
  • You can query sys.foreign_keys view and check for delete_referal_action column. Please see if this is what you were looking for.

    Also there may be many things like triggers that may be causing this. I would suggest you to run SQL Profiler trace and capture the events that occurs. 


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

    • Proposed as answer by Naomi NModerator Wednesday, June 22, 2011 6:33 PM
    • Marked as answer by fweeee Wednesday, June 22, 2011 10:17 PM
    Wednesday, June 22, 2011 12:36 PM
  • EXEC sp_depends @ObjectName

    displays all the dependencies on the given object like stored procedures, views, triggers. 


    Prakash Machiraju
    Wednesday, June 22, 2011 12:44 PM
  • You can query sys.foreign_keys view and check for delete_referal_action column. Please see if this is what you were looking for.

    Also there may be many things like triggers that may be causing this. I would suggest you to run SQL Profiler trace and capture the events that occurs. 

     


     

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.


    Thanks - the sys.foreign_keys was exactly what I was after. Reduced my needle in a haystack to a needle in a small pile of hay. Found the problem - it was a cascading delete.

    Thanks a lot.

    Wednesday, June 22, 2011 10:19 PM