Managing and cleaning BizTalk Server MarkLog database tables according to some Best Practices

All the BizTalk databases which is being backed up by the ‘Backup BizTalk Server’ job, so all databases with the exception of the BAM Star Schema database (BAMStarSchema), has one table called “MarkLog”.

These tables are holding all the transaction marks (they are actually timestamps in a string format), set to a specific database, created by the 3rd step (MarkAndBackUpLog) of the ‘Backup BizTalk Server’ job. This step, MarkAndBackupLog, is responsible for marking the logs for backup, and then backing them up. So each time this step runs, by default every 15 minutes, a string is stored in that table with the following naming convention:

• Log_<yyyy>_<MM>_<dd>_<HH>_<mm>_<ss>_<fff>

Where:

• “Log”: Constant string
• yyyy: The year as a four-digit number.
• MM: The month, from 01 through 12
• dd: The day of the month, from 01 through 31.
• HH: The hour, using a 24-hour clock from 00 to 23.
• mm: The minute, from 00 through 59.
• ss: The second, from 00 through 59.
• fff: The milliseconds in a date and time value.

Again, these marks are timestamps in which the Log backups were set to be made. Example:

• BTS_2014_05_06_14_15_01_327

Unfortunately, BizTalk has no out-of-the-box possibilities to clean up these tables. And the normal procedure is to run the terminator tool to clean it up.

BizTalk Terminator is an excellent tool that allows for common BizTalk problems to be addressed by scripts provided by the BizTalk Team but needs to be used carefully and by users who know what they are doing.

Although most of the time this is an extremely useful tool and our friend, in this particular operation/situation using this tool it’s not really the best option for two reasons:

• Using this tool means that we need to stop our environment, i.e., downtime in our integration platform.
• And if we look at the description of this “PURGE Marklog table” task it says that this operation calls a SQL script that cleans up everything in Marklog table – and maybe this is not the best practices!

I spoke to my dear friend, and one of the best BizTalk Administrator that I know, Tord Glad Nordahl to discuss and know what should be the best practice to maintain these tables:

• Is this information (timestamps) useful for BizTalk Administrators?
• Should I clean all the data inside these tables or should I maintain a history?
• Is safe to clean this information in runtime?

Here are some of the questions that we discussed.

Is this information (timestamps) useful for BizTalk Administrators? Should I clean all the data inside these tables or should I maintain a history?

For the same reason that we maintain a Backup history in the Adm_BackupHistory table controlled by the step “Clear Backup History” of the ‘Backup BizTalk Server’ job. This information is important for example to keep an eye on the backup/log shipping history records to see whether the backup is working correctly and data/logs are restored correctly in the standby environment. The information on the MarkLog tables is also useful for the BizTalk Administration team!

So as long as the MarkLog tables have the same info (data from the same dates) as the backup job days to keep you can safely delete the rest of the information.

As best practices: you should respect the @DaysToKeep parameter that you specify in the “Clear Backup History” step of the ‘Backup BizTalk Server’ job.

And this is why that in my opinion, you shouldn’t use the Terminator tool to perform this operation!

Is safe to clean this information in runtime?

The rows in the Marklog table are not “required” and can be cleaned whenever you want as long the BizTalk Backup Job is not running.

Cleaning MarkLog Tables According to Some of the Best Practices

So the main challenge is how can we safely delete and maintain a history of all MarkLog tables according to some of the best practices described earlier?

My first approach was creating a new job that according to a scheduler would run a stored procedure to delete all the unnecessary information on those tables but I realized I could have two problems with this approach:

• I need to be sure that BizTalk Backup Job wasn’t running performing the backups;
• And I didn’t want to have to places to define the @DaysToKeep and I didn’t want to make unnecessary joins or additional selects

I was trying to find an approach for this operation to be carried out in an easy way and as fast as possible.

So I end up recreating sp_DeleteBackupHistory (that is configured is the Backup BizTalk Server (BizTalkMgmtDb) job in the last step) with a different name sp_DeleteBackupHistoryAndMarkLogsHistory and configure the job to run this step:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndMarkLogsHistory] @DaysToKeep smallint = null, @UseLocalTime bit = 0
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 don't don't delete the set

 Delete history only if history of full Backup exists at a later point of time
 why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
 If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
 */

 declare @PurgeDateTime datetime
 if (@UseLocalTime = 0)
 set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
 else
 set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())

 DELETE [dbo].[adm_BackupHistory]
 FROM [dbo].[adm_BackupHistory] [h1]
 WHERE  [BackupDateTime] < @PurgeDateTime
 AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
 AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')

 /****** Delete MarkLog History from BAMAlertsApplication database ******/
 DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
    WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

 /****** Delete MarkLog History from BAMArchive database* *****/
 DELETE FROM [BAMArchive].[dbo].[MarkLog]
 WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

 /****** Delete MarkLog History from BAMPrimaryImport database ******/
 DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
 WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

 /****** Delete MarkLog History from BizTalkDTADb database ******/
 DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
 WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

 /****** Delete MarkLog History from BizTalkMgmtDb database ******/
 DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
 WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

 /****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
 DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
 WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

 /****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
 DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
 WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

 /****** Delete MarkLog History from SSODB database ******/
 DELETE FROM [SSODB].[dbo].[MarkLog]
 WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep
 END

Steps required to install/configure

• Create the sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure described earlier
• You need to adjust the SQL Script to your environment because some environment may not have all features installed, for example, Business Rules and in this case, BizTalkRuleEngineDb database will not exist.
• Change and configure the 4th step of the ‘Backup BizTalk Server’ job – “Clear Backup History” to call this new stored procedure

Note: Do not change or delete the “sp_DeleteBackupHistory”!

Special thanks to Tord Glad Nordahl for reviewing and all the feedback, and to my friends at DevScope Rui Romano and Pedro Sousa for helping me develop this SQL Query.

Hope you find it useful

Download

THIS SQL SCRIPT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

You can download BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices from GitHub here: