In Microsoft BizTalk Server 2004 and later, the Backup BizTalk Server job fails, and problems occur that relate to limited disk space. This happens when the backup files accumulate over time. This fills up the disk that houses the backup files.
This issue occurs because the SQL Server Agent job that is named "Backup BizTalk Server" does not provide functionality for deleting backup files that have accumulated over time.
To work around this issue, follow these steps:
Start SQL Server Management Studio, and then connect to the BizTalk Management database. By default, this database is named BizTalkMgmtDb.
Click New Query to open a Query Editor window.
Run the following Transact-SQL script to create the sp_DeleteBackupHistoryAndFiles stored procedure:
CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = nullASBEGIN set nocount on IF @DaysToKeep IS NULL OR @DaysToKeep <= 1 RETURN /* Only delete full sets If a set spans a day in such a way that some items fall into the deleted group and the other does not, do not 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) BEGIN IF (@@fetch_status <> -2) BEGIN EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles print @cmd END FETCH NEXT FROM DeleteBackupFiles INTO @cmd END CLOSE DeleteBackupFiles DEALLOCATE DeleteBackupFilesENDGO
Change the "Clear Backup History" step of the Backup BizTalk Server job so that it calls the sp_DeleteBackupHistoryAndFiles stored procedure instead of the sp_DeleteBackupHistory stored procedure.
These steps have only been tested with BizTalk Server 2009 and later running on SQL Server 2008 and later. If you would like to run these steps on earlier versions of BizTalk Server or SQL Server please test thoroughly in a non-production environment first.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.