Truncating data in the EsbException database

Once you got the BizTalk ESB Toolkit running for a while, you would have noticed the EsbException database will begin to grow in size if left unchecked. The main culprit is the MessageData table which holds the entire message receive by BizTalk.

Unfortunately there is no out of the box maintenance script to remove old records from this database. To resolve this issue I decided to write the stored procedure below and execute it every night in a SQL Agent job.

The parameter @DaysToKeep defines how many days worth of exception data you wish to keep. Also I decided to batch delete the rows as not to blow out the transaction log as this database can get quite large. At the end of the script I also shrink the database.

If you wish to deploy this script in a production environment, please test it thoroughly.

USE [EsbExceptionDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*********************************************************************************************
Description:    Deletes records from the tables in a batch style and shrinks the database.

Params:            @DaysToKeep - number of days from the current date to keep alerts.
*********************************************************************************************/
ALTER procedure [dbo].[maint_TruncateOldAlerts]

@DaysToKeep int = 7

as

declare @error int, @rowcount int
declare @DateToDelete datetime
set  @DateToDelete = DATEADD(dd,@DaysToKeep*-1,GETDATE())
print @DateToDelete

--using the fault table as the key table for the insertion date

--dbo.ProcessedFault
print 'Deleting ProcessedFault'
while 1 = 1
begin
    
    delete top(1000) pf from  dbo.Fault f with (nolock) inner join dbo.ProcessedFault pf with (nolock)
    on f.FaultID = pf.ProcessedFaultID where f.InsertedDate < @DateToDelete
    
    If @@rowcount < 1 break
end

--dbo.MessageData
print 'Deleting MessageData'
while 1 = 1
begin
    
    delete top(1000) md from dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID inner join dbo.MessageData md with (nolock) 
    on m.MessageID = md.MessageID where f.InsertedDate < @DateToDelete
    
    If @@rowcount < 1 break
end

--dbo.ContextProperty
print 'Deleting ContextProperty'
while 1 = 1
begin
    
    delete top(1000) cp from dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID inner join dbo.ContextProperty cp with (nolock) 
    on m.MessageID = cp.MessageID where f.InsertedDate < @DateToDelete
    
    If @@rowcount < 1 break
end

--dbo.AuditLogMessageData
print 'Deleting AuditLogMessageData'
while 1 = 1
begin
    
    delete top(1000) almd from dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID inner join dbo.AuditLog al on al.MessageID = m.MessageID
    inner join dbo.AuditLogMessageData almd on al.AuditLogID = almd.AuditLogID
    where f.InsertedDate < @DateToDelete
    
    If @@rowcount < 1 break
end


--dbo.AuditLog
print 'Deleting AuditLog'
while 1 = 1
begin
    delete top(1000) al from  dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID inner join dbo.AuditLog al on al.MessageID = m.MessageID
    where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end

--dbo.AlertSubscriptionHistory
print 'Deleting AlertSubscriptionHistory'
while 1 = 1
begin
    delete top(1000) ash from dbo.Fault f with (nolock) inner join dbo.AlertHistory ah with (nolock)
    on f.FaultID = ah.FaultID inner join dbo.AlertSubscriptionHistory ash with (nolock)
    on ah.AlertHistoryID = ash.AlertHistoryID
    where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end

--dbo.AlertHistory
print 'Deleting AlertHistory'
while 1 = 1
begin
    delete top(1000) ah from dbo.Fault f with (nolock) inner join dbo.AlertHistory ah with (nolock)
    on f.FaultID = ah.FaultID where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end

--dbo.AlertEmail
print 'Deleting AlertEmail'
while 1 = 1
begin
    delete top(1000) ae from dbo.AlertEmail ae where ae.InsertedDate < @DateToDelete
    If @@rowcount < 1 break
end


--dbo.Message
print 'Deleting Message'
while 1 = 1
begin
    delete top(1000) m from dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end

--dbo.Fault
print 'Deleting Fault'
while 1 = 1
begin
    delete top(1000) f from dbo.Fault f with (nolock) where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end


--shrink the database 
DBCC SHRINKDATABASE (EsbExceptionDb, 10);

go

Enjoy.