9

Is there a way to remove Analytics data in Sitecore 9? Similar question for Sitecore 8 which talked about the use of __DeleteAllReportingData and deletion of Mongodbs or the use of Analytics_Database_Manager

I've searched, but didnt find anything about Sitecore 9 Analytics Data.

If any knows, please let us know. Thanks.

2 Answers 2

18

To clean the sql xdb database in Sitecore 9 you need to run bellow script in SQL on every shard that you have .

USE [yourdatabaseprefix.Collection.ShardX]

GO

DELETE FROM [xdb_collection].ContactIdentifiers

Delete from [xdb_collection].ContactFacets

Delete from [xdb_collection].InteractionFacets

Delete from [xdb_collection].Interactions

Delete from [xdb_collection].Contacts   

Updated for 9.1

Add these also for 9.1

Delete from [xdb_collection].ContactIdentifiersIndex
Delete from [xdb_collection].DeviceProfileFacets
Delete from [xdb_collection].DeviceProfiles
Delete from [xdb_collection].DeviceProfileFacets
Delete from [xdb_collection].DeviceProfileFacets 

You also need to clean the interactions out of the processing pool

TRUNCATE TABLE [xdb_processing_pools].[InteractionLiveProcessingPool]

If you use SOLR you need to update xdb solr index:

https://doc.sitecore.net/developers/xp/xconnect/xconnect-search-indexer/rebuild-index/rebuild-solr-index.html

For Azure Search you need to use:

https://doc.sitecore.net/developers/xp/xconnect/xconnect-search-indexer/rebuild-index/rebuild-azure-index.html

You should also to rebuild reporting database:

https://doc.sitecore.net/developers/xp/reporting/rebuild-reporting.html

5
  • It's not just about the DB's, it's also the xDB index that xConnect uses. It's the Reporting database that Sitecore uses. Oct 20, 2018 at 21:59
  • 1
    I will update the answer Oct 21, 2018 at 5:16
  • Thanks @VladIobagiu. really appreciate your comprehensive answer. I'll test it and let you know.
    – Min Thant
    Oct 21, 2018 at 23:06
  • 3
    We did this for a 9.0.1 instance that the processing server quit aggregating data. There were no errors in the logs, but a lot of warnings about not being able to find a contact in xconnect. Sitecore support advised us to clear the data in the InteractionLiveProcessingPool table in the Processing.Pools database and rebuild the reporting database which fixed our issue. Hopefully this will help anyone else who may have the same issue.
    – Drew
    Feb 1, 2019 at 23:15
  • Why are the deletes not a "truncate" on the collections in the same way as the "processing pool" had truncates?
    – Calum
    Feb 20, 2023 at 9:00
2

I agree with Vlad on deleting the xdb shared data. But I would also restore a clean/fresh reporting database. Otherwise you have to rebuild the reporting DB to clean up the analytics reporting.

1
  • In my case, i was still able to see older data even after rebuilding the reporting DB. Later I had to restart the IIS to get cleaned up Analytics reporting. Jul 13, 2021 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.