cancel
Showing results for 
Search instead for 
Did you mean: 

Items are not deleting from JournalDelete table

Sarah_Seftel1
Level 6
Partner Accredited

"There are 437715 items that have been deleted from Vault Store 'Journal Store 1' but have not yet been deleted from the indexes"

 

same error with same numbers on a daily basis, from Journal DB.

the Journal retention is 7 days, so the items in the table no longer exist, but still - those rows are stuck and not deleted at all.

EV version : 9.0.3

 

any ideas what can be done here? as I found similar issue in 10.0.3, but I do not have index synch option in my version yet...

 

Thanks,

Sarah.

1 ACCEPTED SOLUTION

Accepted Solutions

Arjun_Shelke
Level 6
Employee Accredited

The JournalDelete table should only contain records that are within the Transaction History site settings.

The monitoring check will recognise any entries where IndexCommitted = 0, the following query will return a list of the affected archives. 

SELECT COUNT(archiveID) AS 'Number of items', ArchiveID FROM  JournalDelete WHERE IndexCommitted = 0 GROUP  BY ArchiveId 

Check that none of the archives listed have failed indexes, if they do Update them and restart the Admin Service then run the query again. 

If there are still items with IndexCommitted = 0 check if the DeletionDate is within the setting of how long to keep Transaction History for. 

The items should be outside the alloted time period and can be manually updated usuing the following;

 UPDATE JournalDelete SET IndexCommitted = 1 where IndexCommitted = 0 and DeletionDate <=  (GetDate()-[TransactionHistorySetting])

 Restart the Admin Service and re-run the checks, these entries will now have cleared from the JournalDelete table.


 

View solution in original post

11 REPLIES 11

Arjun_Shelke
Level 6
Employee Accredited

The JournalDelete table should only contain records that are within the Transaction History site settings.

The monitoring check will recognise any entries where IndexCommitted = 0, the following query will return a list of the affected archives. 

SELECT COUNT(archiveID) AS 'Number of items', ArchiveID FROM  JournalDelete WHERE IndexCommitted = 0 GROUP  BY ArchiveId 

Check that none of the archives listed have failed indexes, if they do Update them and restart the Admin Service then run the query again. 

If there are still items with IndexCommitted = 0 check if the DeletionDate is within the setting of how long to keep Transaction History for. 

The items should be outside the alloted time period and can be manually updated usuing the following;

 UPDATE JournalDelete SET IndexCommitted = 1 where IndexCommitted = 0 and DeletionDate <=  (GetDate()-[TransactionHistorySetting])

 Restart the Admin Service and re-run the checks, these entries will now have cleared from the JournalDelete table.


 

JesusWept3
Level 6
Partner Accredited Certified

before just slapping on that the index changes have been commited, you may actually want to make sure first that they HAVE been removed from the index, otherwise you will end up with DA searches and what not returning index results for items that do not exist and causing all sorts of errors

In EV10 you could just do a synchronization and have it remove extra items
But in EV9 the only way to remove items from an index that no longer have any DB entries would be a full rebuild of the index

https://www.linkedin.com/in/alex-allen-turl-07370146

GCSPT
Level 5
Employee Accredited Certified

Sarah

Can you run the following on the Journal Store 1 VSDB please.

select count (*) from journaldelete where deletionreason = '3'

Cheers

Sarah_Seftel1
Level 6
Partner Accredited

Will be done on Sunday! will post results after running this.

jprknight-oldax
Level 6

I think I am seeing the same issue in EV8.0.5. So far we are repairing the indexes as this removes the failed items.

This has not reduced the number of items with indexcommitted = 0 though.

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

What is the storagedevice? I've seen this using EMC. After involving EMC, it turned out that a disk was needing to be regenerated, that the socalled Garbagecollection failed, and 'secure delete' also played a part.

In addition, like JPR says, a repair of the index should be helpfull also. Repair first!!

As workaround, we created a new VS for journaling, new archives, and configured journaling to use that. That way, the 'old' archive will only do deletions, which is a good thing,

Regards. Gertjan

Sarah_Seftel1
Level 6
Partner Accredited

updated the IndexCommited to 1, it sorted out the issue.

Arjun_Shelke
Level 6
Employee Accredited

I am glad to know that your issue has been resolved....cheers!!

JesusWept3
Level 6
Partner Accredited Certified

I know this is resolved, but I cannot stress this enough that just slapping the IndexCommited to 1 is not a solution, it is a workaround and could cause a lot of issues in the future if you have items that return in a DA search, or even a user search that can't be retrieved.

https://www.linkedin.com/in/alex-allen-turl-07370146

Sarah_Seftel1
Level 6
Partner Accredited

No DA on this customer site + the fact the the Journal retention category is set to 7 days and then items are deleted + the fact that all the items that were stuck on the table were older than 3 months = happy Sarah smiley

This procedure would not be done by me if the retention was set on forever...

JesusWept3
Level 6
Partner Accredited Certified

but thats kind of my point
You delete the items after 7 days, these items are 3 months old....are you absolutely sure they're not in the index anymore? because setting IndexCommitted = 1 does not delete from the index, you've just fooled EV in to thinking that it has, but any search will return those old items if they werent removed

https://www.linkedin.com/in/alex-allen-turl-07370146