BizTalk heeft in SQL Server een job die ook een cleanup taak heeft en waarbij je zelfs opgeeft hoe lang oude bestanden bewaard mogen blijven. Het rare is alleen dat de fysieke bestanden nooit worden opgeruimd door dit script. De action die dit doet start [sp_DeleteBackupHistory] , maar die stored procedure van BizTalk haalt de files dus niet weg. Om te zorgen dat die dat wel doet laat je deze [sp_DeleteBackupHistoryAndFiles] uitvoeren in plaats van [sp_DeleteBackupHistory], maar die moet je vervolgens nog wel even toevoegen aan de database. Dit script heb ik overigens niet bedacht maar ook op Internet gevonden:

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

Het vervelende is alleen dat dit op nieuwe SQL servers weer faalt. Hij maakt namelijk gebruik van xp_cmdshell en dat staat tegenwoordig standaard eigenlijk altijd uit. Omdat het script een NO_OUTPUT doet en eigenlijk die hele fout niks boeit lijkt alles dus goed te gaan, maar ruimt er nog steeds niks op. Of je dat moet willen moet je even zelf weten, maar de oplossing is dus om xp_cmdshell toe te staan:

-- this turns on advanced options and is needed to configure xp_cmdshell
sp_configure 'show advanced options', '1'
RECONFIGURE
-- this enables xp_cmdshell
sp_configure 'xp_cmdshell', '1' 
RECONFIGURE