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

SYMPTOMS

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.

CAUSE

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.

WORKAROUND

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
    AS
    
    BEGIN
    	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 DeleteBackupFiles
    END
    GO
    
  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.
Important

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.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Properties

Article ID: 982546 - Last Review: September 23, 2011 - Revision: 6.0
APPLIES TO
  • 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
Keywords: 
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 smallbusiness.support.microsoft.com