Welcome once again to another BizTalk Server Best practices, Tips, and Tricks blog post! In my previous blog post, I talked about an essential tip and trick for BizTalk Server administrators:
This was the very first BizTalk Server Best practices, Tips and Tricks blog post of which I hope it to be a long and fun series of blog posts with practical and essential content focused on real cases and daily needs.
Today I’m going to speak about another critical Best practice, Tips and Tricks for BizTalk Server administrators: BizTalk MarkLog tables. This is a very important topic for BizTalk Administrators!
I will promise that the two upcoming Best practices, Tips and Tricks, will be for developers!
An important question that you need to know is: What are BizTalk Server MarkLog tables?
Well, all the out-of-the-box BizTalk databases that are being backup by the Backup BizTalk Server job, so all databases except for the BAM Star Schema database (BAMStarSchema) and, of course, all the custom databases that may be added to this backup process like ESB, UDDI, RosettaNet databases that we address on the previous tip and trick (#1 What do RosettaNet, ESB, and UDDI have in common?), have a table called: MarkLog.
These tables hold 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:
Where:
Again, these marks are timestamps in a string format in which the Log backups were set to be made. Example:
Now, this is where the fun starts. Until BizTalk Server 2016 or at least BizTalk Server 2016 Cumulative Update 6, none of the default BizTalk Server SQL Jobs cleans these tables, and you end up getting a huge amount of records on that table that serves only for logging and keeping the backup historic data consistent… nothing else. You can delete all rows from that table whenever you want as long the BizTalk Backup Job is not running, and you are not breaking anything in the engine or breaking some support rules.
This information is essential or good to have because it is useful for the BizTalk Administration team, 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.
However, if you leave it out of control, you may start getting some issues in the long run:
As a best practice: you should respect the parameter @DaysToKeep present in the 4th step (Clear Backup History) of the Backup BizTalk Server job, i.e., clean everything on that table older than the days specified in the @DaysToKeep parameter.
The second question you want to know is: How do we clean these tables?
And for that, the answer depends on the BizTalk version and Cumulative Update you are running:
As we mentioned earlier, before BizTalk Server 2016 or at least BizTalk Server 2016 Cumulative Update 6, none of the default BizTalk Server SQL Jobs cleaned these tables, and the only official solution to do that is to run the old Terminator tool to clean it up, which nowadays is integrated with the BizTalk Health Monitor.
BizTalk Heath Monitor 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:
Fortunately, there are other and best ways to accomplish this task. We can easily implement these capabilities by recreating and reformulating the existing out-of-the-box sp_DeleteBackupHistory stored procedure. This stored procedure is being used on the last step of the Backup BizTalk Server (BizTalkMgmtDb) job. You can know more about the details implementation of this approach here:
I always told in public events that, in my opinion, Microsoft should take the above script and approach, review it, and include it in the next versions of the product or as a hotfix. And in reality, that was actually what Microsoft did! Well, I’m not saying that they actually took into consideration the above script, but they actually implemented a similar approach.
So the good news is that after BizTalk Server 2016 or at least BizTalk Server 2016 Cumulative Update 6, this problem is taken away from us, and we no longer need to worry about it. It is now part of the out-of-the-box cleaning processes of the BizTalk Server.
Stay tuned for the following BizTalk Server Best practices, Tips, and Tricks.
