Record Deletes/Updates, how to track, find and troubleshoot these issues.

Have you had that issue where you believe something was deleted but you are unable to determine where the record went and how it was deleted? As of London release, we have implemented a delete recovery tool to capture deletes on your instance, if the deletes also caused records to be updated it should also be captured. We also log all the scripts run in scripts - background and all the records affected by that script if the rollback option is ticked.

So if you have one of these cases then this article will go through some of the tables and modules that will potentially help you find when the deletion happened and if captured in time you should be able to recover the data via the rollback contexts.

  There is a number of ways to find and detect affected records:

1. Check shadow tables (sh$<tableName>)
   
   1. The shadow table will be the physical table that stores the information on the database, this can be defined by different table structures: Table Structures FAQ
      
      1. TPC: The table will be the actual table from the UI
      2. TPH: The table will be the base parent table
      3. TPP: The table will be either CMDB or one of the Partitions CMDB$ParX (where X is the Partition
   2. If you are unsure turn on SQL debug when you view a table and you should be able to find the table that that data is coming from.
   3. For this example, incident would be sh$task as it's TPH
   4. This shadow table will show you all deleted records for that table
   5. Important fields are:
      
      1. 1. sh$context - this will tell you the rollback context this deletion is recorded under
         2. sh$first_recorded - this will show you the time this record was deleted.
2. Check deleted records module  (sys_audit_delete)
   
   1. Here you will be able to find all deleted records that are audited.
   2. It has a few important fields
      
      1. 1. transaction - this will tell you the transaction that was run to delete this record.
         2. sys_created_by - this will show you the user who deleted this record
         3. sys_created_on - this will show you the time the record was deleted
3. Check Rollback sequences  (sys_rollback_sequence)
   
   1. This table records all the updates/deletes that are triggered as part of the rollback functionality
      
      1. 1. To help search this table if you have the sys_id of the record you can search via the document id column
         2. If you found the context from the shadow table or another method you can use that to search via the context column
         3. With the context you will be able to show all the records that were deleted and updated via that context
4. Check Rollback incremental changes (sys_rollback_incremental)
   
   1. 1. This table records the element updates for the specific record
         
         1. To help search this table if you have the sys_id of the record you can search via the document id column
         2. If you found the context from the shadow table or another method you can use that to search via the context column
         3. This gives more information with the sys_rollback_sequence for updates and will tell you specifically which columns were updated EG: short_description of an incident.
         4. Important fields are:
            
            1. Element Name - The column that was adjusted
            2. title="">Upgraded value - This will show the new value of that element
            3. Txn ID - The transaction that made this change
            4. When - This will show you when this record was updated (NOTE: PRB1343251 - “When” field in sys_rollback_incremental is inserted as PST instead of UTC so for this field you will need to add 6 or 7 hours depending on daylight savings)
5. Check Script execution history (sys_script_execution_history)
   
   1. 1. The execution history will show all scripts run via scripts - background
      2. Each history will provide an affected records summary
      3. More information How to check what background scripts have been run on your instance?
6. Look through sys_audit for newvalue = 'DELETED'
   
   1. This can show you who deleted and when.

Looking through these tables you should hopefully be able to find the deletion of the record that you were looking for.

More information can be found in our docs: Roll back and delete recovery

If you still have issues finding a deleted record you can always contact Support and we will assist you to the best of our ability to find your deleted records.

Also to note, with deletions timing is everything so the sooner you let Support know of the deletion the better chances we have to track down the deletion, also if you use the steps above you can provide support with your findings to help us track down the deletion quicker.

Table Structures FAQ

How to check what background scripts have been run on your instance?

Roll back and delete recovery