ESB Alert Service Exception

Question

suddenly our ESB boxes have started throwing errors in the event log that says

Cannot insert duplicate key in object 'dbo.ProcessedFault'. Cant seem to find any clues.

-----------------------------------------------------------

Message: HandlingInstanceID: 9a39db0b-1828-4594-938e-c49184eaba77

An exception of type 'Microsoft.Practices.ESB.AlertService.AlertException' occurred and was caught.

---------------------------------------------------------------------------------------------------

10/05/2011 08:04:40

Type : Microsoft.Practices.ESB.AlertService.AlertException, BizTalk ESB Exception Notification, Version=2.1.0.0, Culture=neutral, PublicKeyToken=cfbbbea05ab1e4a2

Message : Class: Microsoft.Practices.ESB.AlertService.DataAccess.AlertDataAccess Method: Void EndBatch(Microsoft.Practices.ESB.AlertService.DataSets.Batches, Microsoft.Practices.ESB.AlertService.DataSets.ProcessedFaults, Microsoft.Practices.ESB.AlertService.DataSets.Emails) : Exception: Violation of PRIMARY KEY constraint 'pk_ProcessedFaults_ProcessedFaultsID'. Cannot insert duplicate key in object 'dbo.ProcessedFault'.

The statement has been terminated.

---

mayur macwan

Answer 1

Not sure, are there any other errors that occurred before this? It's as if something is stuck or trying to insert Nulls or something.

Can you turn on Esb Tracing and use Debug View or something to view the errors /log?

 

---

MCT, MCSD.NET, MVP

Answer 2

No resolution found yet.

After these errors, i restart the Biztalk ESB Exception services and it stops the errors for some time and then it starts showing up again.

 

---

mayur macwan

Answer 3

This is a primary key constrain violation. This means that a fault is somehow using the same ID and hence cannot get inserted in the EsbExceptionsDb. You need to cleanup this database as it grows very fast. So it is always a best practice to have a backup and archive strategy for it. To resolve around this error empty and fillup again the ProcessedFaults table by simply executing the following.

Delete from [EsbExceptionDb].[dbo].[ProcessedFaults]
GO

Insert into [EsbExceptionDb].[dbo].[ProcessedFaults] select [FaultID] from [EsbExceptionDb].[dbo].[Faults]