Before any action to be taken, please make sure we have database full back up in place.
- First we will run the below query to identify how many data we have on each of the following tables.
Select Count (*) From POLICYINSTANCES WITH (NOLOCK) where TimeEnded > DATEADD(DD,0,'2020-09-07')
Select Count (*) From EVENTS WITH (NOLOCK) where DateTime > DATEADD(DD,0, '2020-09-07')
Select Count (*) From OBJECTINSTANCES WITH (NOLOCK) where EndTime > DATEADD(DD,0, '2020-09-07')
- If the count is within 20k or 30k, we can keep it else we should remove those data. If we need to keep it, we will create a backup table by running the below query.
SELECT * INTO EVENTS_backup FROM EVENTS WITH (NOLOCK) where DateTime > DATEADD(DD,0, '2020-09-07')
SELECT * INTO POLICYINSTANCES_backup FROM POLICYINSTANCES WITH (NOLOCK) where TimeEnded > DATEADD(DD,0,'2020-09-07')
SELECT * INTO OBJECTINSTANCES_backup FROM OBJECTINSTANCES WITH (NOLOCK) where EndTime > DATEADD(DD,0, '2020-09-07')
- Now Follow the action plan as mentioned in the below article.
https://www.souravmahato.com/delete-orphan-entries-from-scroch-database/
- Now insert the backup data from backup tables to production tables.
Insert into EVENTS Select * from EVENTS_backup with (nolock)
Insert into POLICYINSTANCES Select * from POLICYINSTANCES_backup with (nolock)
Insert into OBJECTINSTANCES Select * from OBJECTINSTANCES_backup with (nolock)
- Once done, remove the backup tables from database or truncate the data for the backup tables as created during this Operation.
– We don’t need to do any changes for rest of all the delete or remove commands as those will be taken care depending on the settings we have for data purging.
