Table of Contents

Important

This wiki post is a preliminary Knowledge Base article. The official version of this article has been published to http://support.microsoft.com/kb/982546.

Summary

The SQL Server Agent job named "Backup BizTalk Server" does not provide functionality for deleting backup files that have accrued over time. This can cause the disk that houses the backup files to fill up which can eventually cause the Backup BizTalk Server job to fail or other problems associated with limited disk space to occur.

More Information

To workaround this issue follow these steps:

  1. Start SQL Server Management Studio.
  2. Launch the Query Editor.
  3. Connect to the BizTalk Management database (BizTalkMgmtDb by default)
  4. Execute the following SQL Script to create the stored procedure sp_DeleteBackupHistoryAndFiles

    CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
    AS
    BEGIN
        set nocount on
          IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
          RETURN
        /*
          Only delete full sets
          If a set spans a day such that some items fall into the deleted group and the other doesn't, 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
  5. Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles rather than calling sp_DeleteBackupHistory.

  6. Enable xp_cmdshell for the SQL Server instance if it is disabled (xp_cmdshell is disabled by default). See the SQL Server online help for information about enabling xp_cmdshell.

Acknowledgements

Many thanks to William Chesnut (http://biztalkbill.com/)  for the SQL query code used above to delete old backup files.

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.