BizTalk 2010 Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 11)

By default, the following BizTalk jobs aren’t configured and enabled upon installation.

Backup BizTalk Server (BizTalkMgmtDb)
DTA Purge and Archive (BizTalkDTADb)
MessageBox_Message_Cleanup_BizTalkMsgBoxDb

If you want these functionalities you must configure and enable them.

How to configure Backup BizTalk Server (BizTalkMgmtDb)

This Job consists of four steps:

Step 1 – Set Compression Option – Enable or disable compression during backup.
Step 2 – BackupFull – Performs full database backups of the BizTalk Server databases.
Step 3 – MarkAndBackUpLog – Backs up the BizTalk Server database logs.
Step 4 – Clear Backup History – Specifies how long the backup history is kept.

Step 1 – Set Compression Option

This job step calls a stored procedure named sp_SetBackupCompression on the BizTalk management database (BizTalkMgmtDb by default) to set the value on the adm_BackupSettings table.

The original script is:

exec [dbo].[sp_SetBackupCompression] @bCompression = 0 /*0 - Do not use Compression, 1 - Use Compression */

The stored procedure has only one parameter:

@bCompression: By default, Set Compression Option is set to 0, which makes backup compression off by default. To change the default, change Set Compression Option to 1.

Change the script to:

exec [dbo].[sp_SetBackupCompression] @bCompression = 1 /*0 - Do not use Compression, 1 - Use Compression */

For more information see Tiago Almeida article: BizTalk Server 2010 Backup Compression

Step 2 – BackupFull

The original script is:

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '...destination path...' /* location of backup files */

Where:

Frequency (@Frequency): The default is d (daily). This is the recommended setting. Other values include h (hourly), w (weekly), m (monthly), or y (yearly).
Name (@MarkName): The default is BTS. The name is used as part of the backup file name.
Location of backup files (@BackupPath): Replace ‘<destination path>’ with the full path (the path must include the single quotes) to the computer and folder where you want to back up the BizTalk Server databases.

There are also three optional parameters:

Force full backup after partial backup failures (@ForceFullBackupAfterPartialSetFailure): The default is 0 when not specified, which means that if a log backup fails, no full backups are done until the next full backup frequency interval is reached. Replace with 1 if you want a full backup to be made whenever a log backup failure occurs.
Local time hour for the backup process to run (@BackupHour): The default is NULL when not specified, which means that backup job will not be associated with the time zone of the BizTalk Server computer and will run at midnight UTC time (0000). If you want to backup to run at a particular hour in the time zone of the BizTalk Server computer, specify an integer value from 0 (midnight) to 23 (11 PM) as the local time hour for the BackupHour parameter.
Use local time (@UseLocalTime): This is an extra parameter that you can also add that tells the procedure to use local time
- The default value is 0
  - If set to 0, then it uses current UTC time – GETUTCDATE() – 2007-05-04 01:34:11.933
  - If set to 1, then it uses local time – GETDATE() – 2007-05-03 18:34:11.933

Change the script to:

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '...your_destination_path...\BizTalk Database\Full' /* location of backup files */

Step 3 – MarkAndBackUpLog

The MarkAndBackupLog step is responsible for marking the logs for backup, and then backing them up.

The original script is:

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '...destination path...' /* location of backup files */

Where:

@MarkName: Log mark name is part of the naming convention for backup files:
- <Server Name>_<Database Name>_Log_< Log Mark Name >_<Timestamp>
@BackupPath:. You must change the destination path this to a valid one. It may be local or an UNC path to another server.

There is also one optional parameter:

@UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */,'...destination path...' /*location of backup files */ , 1

Change the script to:

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '...your_destination_path...\BizTalk Database\Logs' /* location of backup files */

Step 4 – Clear Backup History

The original script clears out the instances in the MarkLog table older than 14 days:

exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14

Where:

@DaysToKeep: specifies how long the backup history is kept in the Adm_BackupHistory table. Periodically clearing the backup history helps to maintain the Adm_BackupHistory table at an appropriate size. The default value for the DaysToKeep parameter is 14 days.

There is also one optional parameter:

@UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time

exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14 , @UseLocalTime =1

In this particular case, I like to leave the default settings.

Note:
This job step does not provide functionality for deleting backup files that have accumulated over time.

You can solve this problem by implementing you custom sp_DeleteBackupHistory:

Start SQL Server Management Studio, and then connect to the BizTalk Management Database. By default, this database is named BizTalkMgmtDb.
Click New Query to open a Query Editor window.
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 &lt;= 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()) &lt; @DaysToKeep)
 DECLARE @cmd varchar(400)
    OPEN DeleteBackupFiles
    FETCH NEXT FROM DeleteBackupFiles INTO @cmd
    WHILE (@@fetch_status &lt;> -1)
    BEGIN
       IF (@@fetch_status &lt;> -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

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.

How to configure DTA Purge and Archive (BizTalkDTADb)

This job automates the archiving of tracked messages and the purging of the BizTalk Tracking database to maintain a healthy system and to keep the tracking data archived for future use.

And it’s configured to call the stored procedure dtasp_BackupAndPurgeTrackingDatabase, which uses the six parameters you must configure in this job:

@nLiveHours: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. The default is 0 hours.
@nLiveDays: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. The default interval is 1 day.
@nHardDeleteDays: All data (even if incomplete) older than this will be deleted. The time interval specified for HardDeleteDays should be greater than the live window of data. The live window of data is the interval of time for which you want to maintain tracking data in the BizTalk Tracking (BizTalkDTADb) database. Anything older than this interval is eligible to be archived at the next archive and then purged. The default is 30 days.
@nvcFolder: Folder in which to put the backup files.
@nvcValidatingServer: Server on which validation will be done. A null value indicates no validation is being done. The default is NULL.
@fForceBackup: Default is 0. This is reserved for future use.
For more information: How to Purge Data from the BizTalk Tracking Database

The original script after installing BizTalk looks like this:

exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --

This means that:

Any completed instance that is older than the live days plus live hours will be deleted, as will any associated data.
Any data older than the HardDeleteDays will be deleted.

Normally I use these configurations for production environments:

exec dtasp_BackupAndPurgeTrackingDatabase 0, 10, 20, '...destination path...', null, 0

For more information: How to Configure the DTA Purge and Archive Job

However in a development machine, we don’t need to maintain the archived tracking data, so I just purge it periodically. BizTalk gives you the option to Archive and Purge the tracking data or just simple purge the data without archiving:

Change the SQL Statement inside “DTA Purge and Achieve” SQL Job to

 declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup

MessageBox_Message_Cleanup_BizTalkMsgBoxDb

Removes all messages that are not referenced by any subscribers in the BizTalkMsgBoxDb database tables.

Important:
This job is also started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. Therefore, we recommend that you disable this job.

16 thoughts on “BizTalk 2010 Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 11)”

Nuno Reis says:

January 27, 2011 at 1:03 am

We used the purge and archive 3 weeks ago in our test environment when the disk became full. It runned for hours (4 hours first time, then the entire night) with no signs of progress. We end up by truncating the tables manually since it was not working.

Now the DB has a normal size and all the maintenance jobs work perfectly, including this one.

sb says:

January 13, 2012 at 5:53 am

Hi Sandro, your posts are helpful. Wanted to inquire the sql job monitor biztalk server fails in our environment. is there anyway to have it run successfully other than applying cumulative updates. Thanks a lot in advance !!

1. Sandro Pereira says:
  
  January 13, 2012 at 9:19 am
  
  Hi Suruchi,
  
  That depends, have you created more hosts within the BizTalk group?
  
  If so you need to install CU (this problem was fixed in BizTalk 2010 CU1).
  
  If not use BizTalk Terminator to resolve issues identified by BizTalk MsgBoxViewer and the BizTalk 2010 Monitor BizTalk Server Job: http://blogs.msdn.com/b/biztalkcpr/archive/2009/05/06/using-biztalk-terminator-to-resolve-issues-identified-by-biztalk-msgboxviewer.aspx
  
  1. sb says:
    
    January 13, 2012 at 6:24 pm
    
    Thanks Sandro. Yes, we have many hosts within out BizTalk group. I know we can apply the CU but ours is a very regulated and controlled environment and so this will require much planning process and testing. So I was looking for an alternate workaround to make this process work like directly changing stored procedures involved etc to make it work. But Havent been successful so far. Thanks for your reply and feedback !!
    
Pingback: Biztalk Server 2010 Kurulumu–Biztalk Server’ın konfigürasyonu | Microsoft günlükleri by barisca
DenMak says:

March 7, 2012 at 3:46 pm

Hello! Thank You! Very helpfull post!
But one moment frustrated me a little bit )). It was about sp_DeleteBackupHistoryAndFiles. You Wrote: “Start SQL Server Management Studio, and then connect to the BizTalk MessageBox database. By default, this database is named BizTalkMsgBoxDb” and create procedure. But this procedure should be created in BizTalkMgmtDb to be successfully run in job.

1. Sandro Pereira says:
  
  March 7, 2012 at 4:02 pm
  
  Hi DenMak,
  You are right, thanks for the warning, I’ve fixed the post.
  
Anonymous says:

July 6, 2012 at 2:34 am

You need to be a part of a contest for one of the highest quality websites on the web. I am going to highly recommend this web site!

Pingback: Biztalk Server 2010 Kurulumu – Biztalk Server’ın Konfigürasyonu | Mshowto
Leonid Ganeline says:

August 9, 2013 at 6:43 pm

Hey Sandro,

Thank you for the improvements in the Backup job. I’m using it in all by installations now. Works file. Thanks a lot!
Did you try it in the BizTalk 2013? I’ve tried it but for some reason the old backup files are not deleted.

1. Sandro Pereira says:
  
  August 9, 2013 at 11:11 pm
  
  Hi Leonid,
  Thanks! I didn’t try in BizTalk Server 2013… instead I create a Maintenance Plan to delete BizTalk Database Backups files.
  
  The main advantage of this approach is that will allow us more flexibility for further changes and we can also use them to other backup’s that we have.
  
  1. Leonid Ganeline says:
    
    August 10, 2013 at 1:53 am
    
    Nice feature. Works as needed.
    
David Ramey says:

September 5, 2013 at 8:28 pm

While sp_DeleteBackupHistoryAndFiles takes care of the backups created in the management database, what cleans up the backups in from the BizTalkDTADb database? The files created by dtasp_BackupAndPurgeTrackingDatabase just seem to collect and are never removed.

What do you do with yours?

1. Sandro Pereira says:
  
  September 5, 2013 at 9:30 pm
  
  Hi David,
  as I mention in one of my comment… I don’t like to mess around with the BizTalk Jobs, they do their critical job very well and because they are so critical to the welfare of the environment I don’t like to change the default store procedures.
  
  Instead I like to create a Maintenance Plan to delete all BizTalk Backups files like I explain in one of my posts: http://sandroaspbiztalkblog.wordpress.com/2013/06/26/how-to-create-a-maintenance-plan-to-delete-biztalk-database-backups/.
  
  The main advantage of this approach is that will allow us more flexibility for further changes and we can also use them to other backup’s that we have.
  
  1. David Ramey says:
    
    September 5, 2013 at 9:45 pm
    
    Thanks. The only reason I modified the job was because I found it referenced in the knowledge base: http://support.microsoft.com/kb/982546
    
    For the same reason, I didn’t want to create a job that would mess with files created by a BizTalk job. But, that sounds like what I’ll have to do.
    
    Thank you very much for your fast response.
    
Pingback: How to Count and Purge your dta_DebugTrace in BizTalkDTADb - MyLifeIsMyMessage.net

BizTalk 2010 Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 11)

How to configure Backup BizTalk Server (BizTalkMgmtDb)

Step 1 – Set Compression Option

Step 2 – BackupFull

Step 3 – MarkAndBackUpLog

Step 4 – Clear Backup History

How to configure DTA Purge and Archive (BizTalkDTADb)

MessageBox_Message_Cleanup_BizTalkMsgBoxDb

Related links

Author: Sandro Pereira

16 thoughts on “BizTalk 2010 Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 11)”

Leave a Reply Cancel reply