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

Article translations Article translations
Article ID: 982546 - View products that this article applies to.
Expand all | Collapse all


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 = null
    	set nocount on
    	IF @DaysToKeep IS NULL OR @DaysToKeep <= 1
    	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)
            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
  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: September 23, 2011 - 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

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from