"Ghost" records after mass-deleting with deleteMultiple()

[Max Nowak]

Hi,

I'm currently having a strange problem. On a customer's dev instance, I wanted to clear out a large table (~650k records), which extends the task table. I did this by running a pretty basic Fix Script in background that looked like this:

var gr = new GlideRecord('custom_table_name');
gr.deleteMultiple();

After a while, I checked the progress of the Fix Script, and was surprised when it showed as successfully finished, as I had expected it to run for at least another 10 hours. I then checked the table and saw that the list view was empty, but the counter at the bottom still showed ~620k records:

 

Now, this likely isn't an ACL issue, since I have the admin role, and all of the data was previously visible to me.

I have no idea on how to get rid of those "ghost" records. Here's what I've tried so far, which didn't help at all:

- Running background scripts with deleteMultiple() or iterating over all records and calling deleteRecord()

- Clicking on the "Delete all records" UI Action on the table form

- Creating a Delete Job and letting that run

- Clearing the instance cache

- Logging in as a different admin user

- Create new records in that table (worked fine) and then delete them again with deleteMultiple() (also worked, but it only deleted the new records I created)

Here are some interesting observations:

- This table, as mentioned, extends Task. If I query the task table with sys_class_name=custom_table_name, it returns 0 records.

- If I query the custom table directly with a GlideRecord query, it returns 0 records

- If I query the table using GlideAggregate and adding the COUNT aggregate, it returns 622k records.

- The filters in the list view still work. I can filter specific fields and watch the counter at the bottom go down, but I don't see any records.

Does anybody know what to do?

[Bert_c1]

Seems you may need to create a Support case to fix your table.

[Sohail Khilji]

Hi @Max Nowak ,

Given that GlideRecord and direct table queries return no records, but GlideAggregate shows a count,

i strongly suggests that the records might be in a state where they are not being properly indexed or....

accessible through standard queries,

yet the system still acknowledges their existence in some form.

Try the below :

Examine Audit and history records

It's possible that the indexes on your custom table have become corrupted or are out of sync with the actual data. so check for indexes

If non of these helps, i recommend you to check with servicenow Hi SUPPORT !