Gitesh PortfolioAlways On

Delete XDB/XConnect collection data from Shard tables. You can delete contacts and interactions based on last modified date field in the table.

 

--Very fast query for performance
 
DECLARE @tempTable TABLE (ContactID varchar(1000))
 
INSERT INTO @tempTable
SELECT TOP 100 ContactId FROM [xdb_collection].[Contacts] WHERE LastModified < '2020-10-02 23:18:30.5847533' ORDER BY ContactId ASC
 
SELECT COUNT(*) FROM @tempTable
 
DELETE ci FROM [xdb_collection].[ContactIdentifiers] ci
INNER JOIN @tempTable t ON ci.ContactId = t.ContactID
 
DELETE cii FROM [xdb_collection].[ContactIdentifiersIndex] cii
INNER JOIN @tempTable t ON cii.ContactId = t.ContactID
 
DELETE cf FROM [xdb_collection].[ContactFacets] cf
INNER JOIN @tempTable t ON cf.ContactId = t.ContactID
 
DELETE cif FROM [xdb_collection].[InteractionFacets] cif
INNER JOIN @tempTable t ON cif.ContactId = t.ContactID
 
DELETE cit FROM [xdb_collection].[Interactions] cit
INNER JOIN @tempTable t ON cit.ContactId = t.ContactID
 
DELETE c FROM [xdb_collection].[Contacts] c
INNER JOIN @tempTable t ON c.ContactId = t.ContactID

Another way to do this without any conditions.

delete
FROM [xdb_collection].[ContactIdentifiers]
 
delete
FROM [xdb_collection].[ContactIdentifiersIndex]
 
--DECLARE @cnt INT = 0;
 
--WHILE @cnt < 1000
--BEGIN
--   delete TOP(100000)
--FROM [xdb_collection].[ContactFacets]
--   SET @cnt = @cnt + 1;
--END;
 
delete
FROM [xdb_collection].[ContactFacets]
 
--DECLARE @cnt1 INT = 0;
--WHILE @cnt1 < 10000
--BEGIN
--   delete TOP(100000)
--FROM [xdb_collection].[InteractionFacets]
--   SET @cnt1 = @cnt1 + 1;
--END;
   
delete
FROM [xdb_collection].[InteractionFacets]
 
--DECLARE @cnt2 INT = 0;
-- WHILE @cnt2 < 10000
--BEGIN
--   delete TOP(100000)
--FROM [xdb_collection].[Interactions]
--   SET @cnt2 = @cnt2 + 1;
--END;
 
 
delete
FROM [xdb_collection].[Interactions]
 
-- DECLARE @cnt3 INT = 0;
-- WHILE @cnt3 < 20000
--BEGIN
--   delete TOP(10000)
--FROM [xdb_collection].[Contacts]
--   SET @cnt3 = @cnt3 + 1;
--END;
 
delete
FROM [xdb_collection].[Contacts]
 
select schema_name(tab.schema_id) + '.' + tab.name as [table],
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb,
	sum(part.rows) as NoOfRecords
from sys.tables tab
    inner join sys.indexes ind 
        on tab.object_id = ind.object_id
    inner join sys.partitions part 
        on ind.object_id = part.object_id and ind.index_id = part.index_id
    inner join sys.allocation_units spc
        on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc