Purging Interactions

Doing a mass delete involves two separate steps/queries, which are fairly straightforward.  They can be run manually or set up as scheduled jobs.  Generally the idea is to run #1 as a scheduled job, and then #2 manually as needed.  Here are the details if you’d like to review them before:

1.      Mass Interaction Delete – This stored procedure deletes all interactions that match a specific clause, and can be called using the following script below.  From our experience this procedure can take a while if you are deleting a large number of interactions. A typical installation on SQL Server 2008 R2 with 4 cpu cores and 8GB ram can be 35,000 interactions per hour, but obviously depends on overall server specs and other activities.  The <delete clause> below specifies the range of interactions you want to delete, which could be anything beyond a specific date, anything in a topic, etc, or a combination of multiple clauses.

DECLARE @RC int

DECLARE @WhereClause nvarchar(512) = N' <Delete Clause>'

DECLARE @Max int = <MaxInteractionsToDelete>

EXECUTE @RC = [<iservicedbname>].[dbo].[iServiceInteractionsMassDelete]

  @WhereClause

  ,@Max

GO

2.      Cleanup – This is a separate stored procedure that can be used to cleanup emails and other files unused after an interaction delete.  It tends to be much more process intensive, and will likely have an impact on users if they are trying to use iService at the same time. There aren’t any variables to be called, it is simply executed as shown below.

DECLARE @RC int

EXECUTE @RC = [<iservicedbname>].[dbo].[iServiceFilesTextCleanup]

GO