ESBExceptionDB Maintance

ESBExceptionDB is a Database that is used to capture the Exceptions and used for Alert notifications, resubmitting of the messages ( with a bit of customization).

But over a period of time this Database will start to accumulate the messages. We don't have out-box jobs to clean the Database. We need to set up custom job similar to  dtasp_BackupAndPurgeTrackingDatabase to clean Faults. Below is the script that can be used to Maintain the DB. We can further Enhance ESB Portal to allow User to delete the messages after reviewing the Expection, but in any case we need a custom job/script to clean up the DataBase.

USE [EsbExceptionDb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_PurgeEsbExceptionDb]
    @DaysToKeep    INT = 30
AS
BEGIN
/*
=============================================
Example:
EXEC [dbo].[usp_PurgeEsbExceptionDb] @DaysToKeep = 30
=============================================
*/
    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 ProcessedFault 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

MQSC Adapter fails with reason code 2394 when it uses UTF-8 encoding

This morning I ran into the below error while testing a connectivity using MQSC adapter and UTF-8 encoding after installing CU3 for Host Integration Server 2013. I am able to read the messages out of queue without UTF-8 encoding in receive location, but UTF-8 is causing the issue.

The adapter "MQSC" raised an error message. Details "Failure encountered while attempting to get message from queue. queue = {queueName}, queueManager = {queueManager}, reasonCode = 2394".

This issue started post CU3 Installation only.. I was able to reproduce this issue in multiple servers….

Upon some binging.. I was able to find an article which talk about an issue with CU3 for HIS 2013 and UTF-8 encoding. I was not able to find the Hotfix download.. so instead I uninstalled the CU3 for Host Integration Server 2013 and wolaa the issue is resolved… I am able to use MQSC adapter to read messages from the Queue using UT-8 encoding in the receive location.

I won’t not say this is a clean solution but if you run into this issue and had the CU3 installed for HIS 2013 uninstalling it can resolve the issue… Also check the below hotfix’s that CU3 contains which could impact your system due to uninstallation of CU3.

CU3 Details @ : https://support.microsoft.com/en-us/help/3019572/cumulative-update-3-for-host-integration-server-2013

I will be searching for a better solution …..

BizTalk Server 2013 R2

Host Integration Server 2013

Hope this help... If you have any question please feel to ping me .. 
Happy Integrating ..