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:
- Start SQL Server Management Studio.
- Launch the Query Editor.
- Connect to the BizTalk Management database (BizTalkMgmtDb by default)
- 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 )
datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeepAND
[BackupSetId]
NOT
IN
(
SELECT
[BackupSetId]
FROM
[dbo].[adm_BackupHistory] [h2]
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
- Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles rather than calling sp_DeleteBackupHistory.
-
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.