Since the Backup History Delete step does not clean up the backup files, I have make a stored procedure that cleans up the files also, add the stored procedure below to the BizTalk Management Database and change step3 of the backup job by adding “AndFiles“ to the end of the exiting stored prorcedure call.

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null



 set nocount on

  IF @DaysToKeep IS NULL OR @DaysToKeep <= 0



  Only delete full sets

  If a set spans a day such that some items fall into the deleted group and the other don’t don’t delete the set


DECLARE DeleteBackupFiles CURSOR

FOR SELECT ‘del ‘ + [BackupFileLocation] + ‘\’ + [BackupFileName] FROM [adm_BackupHistory]

WHERE  datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep

 AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )

 DECLARE @cmd varchar(400)

OPEN DeleteBackupFiles

 FETCH NEXT FROM DeleteBackupFiles INTO @cmd

WHILE (@@fetch_status <> -1)


            IF (@@fetch_status <> -2)


                        EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

                        delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles

                        print @cmd


            FETCH NEXT FROM DeleteBackupFiles INTO @cmd


CLOSE DeleteBackupFiles

DEALLOCATE DeleteBackupFiles