BizTalk ESB: Cleaning/Purge ESBExceptionDb for Fault Messages

USE [EsbExceptionDb]
GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[x_Delete_ESB_Faults]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[x_Delete_ESB_Faults]
GO USE [EsbExceptionDb]
GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[x_Delete_ESB_Faults]
@DaysToKeep INT = 7
AS BEGIN /* ============================================= Example: EXEC [dbo].[x_Delete_ESB_Faults] @DaysToKeep = 1 ============================================= */ DECLARE @currentDateTime DATETIME, @deleteFromDateTime DATETIME; SET @currentDateTime = GETUTCDATE(); SET @deleteFromDateTime = @currentDateTime - @DaysToKeep; SET NOCOUNT ON; /*Deleting all the records from ContextProperty table*/ DELETE conprop FROM [dbo].[ContextProperty] conprop
INNER JOIN (SELECT msg.MessageID FROM [dbo].[Message] (NOLOCK) msg
INNER JOIN (SELECT fault.FaultID FROM [dbo].[Fault](NOLOCK) fault WHERE fault.InsertedDate < @deleteFromDateTime) faultsToDelete
ON faultsToDelete.FaultID = msg.FaultID) msgIdsToDelete
ON msgIdsToDelete.MessageID = conprop.MessageID
/*Deleting all the records from ContextProperty table*/ DELETE procfaults FROM [dbo].[ProcessedFault](NOLOCK) procfaults
INNER JOIN (SELECT fault.FaultID FROM [dbo].[Fault](NOLOCK) fault WHERE fault.InsertedDate < @deleteFromDateTime) faultsToDelete
ON procfaults.ProcessedFaultID = faultsToDelete.FaultID
/*Deleting all the records from MessageData table*/ DELETE msgdata FROM [dbo].[MessageData] msgdata
INNER JOIN (SELECT msg.MessageID FROM [dbo].[Message](NOLOCK) msg
INNER JOIN (SELECT fault.FaultID FROM [dbo].[Fault](NOLOCK) fault WHERE fault.InsertedDate < @deleteFromDateTime) faultsToDelete
ON faultsToDelete.FaultID = msg.FaultID) msgIdsToDelete
ON msgIdsToDelete.MessageID = msgdata.MessageID
/*Deleting all the records from Message table*/ DELETE msg FROM [dbo].[Message] msg
INNER JOIN (SELECT fault.FaultID FROM [dbo].[Fault](NOLOCK) fault WHERE fault.InsertedDate < @deleteFromDateTime) faultsToDelete
ON msg.FaultID = faultsToDelete.FaultID
/*Deleting all the records from Fault table*/ DELETE fault FROM [dbo].[Fault](NOLOCK) fault WHERE fault.InsertedDate < @deleteFromDateTime; /*Deleting all the records from AlertSubscriptionHistory table*/ DELETE ash FROM [dbo].[AlertSubscriptionHistory] (NOLOCK) ash WHERE ash.InsertedDate < @deleteFromDateTime; /*Deleting all the records from AlertHistory table*/ DELETE ah FROM [dbo].[AlertHistory] (NOLOCK) ah WHERE ah.InsertedDate < @deleteFromDateTime; /*Deleting all the records from AlertEmail table*/ DELETE ae FROM [dbo].[AlertEmail] (NOLOCK) ae WHERE ae.InsertedDate < @deleteFromDateTime; SET NOCOUNT OFF; END GO