You are currently offline, waiting for your internet to reconnect

The "Backup BizTalk Server" job fails when backup files accumulate over time in the Microsoft BizTalk Server database server

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:
  1. Start SQL Server Management Studio, and then connect to the BizTalk Management database. By default, this database is named BizTalkMgmtDb.
  2. Click New Query to open a Query Editor window.
  3. 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
  4. 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.

Article ID: 982546 - Last Review: 09/23/2011 10:50:00 - Revision: 6.0

  • Microsoft BizTalk Server 2009 Standard
  • Microsoft BizTalk Server 2009 Developer
  • Microsoft BizTalk Server 2009 Enterprise
  • Microsoft BizTalk Server 2009 Branch
  • Microsoft BizTalk Server Branch 2010
  • Microsoft BizTalk Server Developer 2010
  • Microsoft BizTalk Server Enterprise 2010
  • Microsoft BizTalk Server Standard 2010
  • kbtshoot kbexpertiseadvanced kbprb kbsurveynew KB982546