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

Posted: May 6, 2014 in BizTalk
Tags: , , , , , , , , , , , , , ,

All the BizTalk database 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 3th 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 each 15 minutes, a string is stored on 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 this 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. See also: Clean up the MarkLog table with Terminator

Terminator-Tool-Purge-MarkLogs

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 times this is 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 a 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 these information (timestamps) useful for BizTalk Administrators?
  • Should I clean all the data inside this tables or should I maintain a history?
  • Is safe to clean this information in runtime?

Were some of the questions that we discussed.

Is these information (timestamps) useful for BizTalk Administrators? Should I clean all the data inside this 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 back is working correctly and data/logs are restored correctly in the stand by environment. The information on the MarkLog tables are 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 a 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 challenger 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 that 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

sp_DeleteBackupHistoryAndMarkLogsHistory

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

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

Hope you find it useful

You can download the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery

 

Comments
  1. Jimmy H says:

    Hi ,
    using your script sp_DeleteBackupHistoryAndFiles from https://sandroaspbiztalkblog.wordpress.com/2013/05/26/biztalk-2013-installation-and-configuration-configure-biztalk-server-sql-jobs-part-15/
    as a base add adding the code below I have a solution that worked out very well. By using the view admv_BackupDatabases to get server and databases names the script becomes more dynamic and it works no matter how
    your server and biztalk database configuration looks like. I like the idea of having all the clean up done in the same script as the actual backup is done as this makes sure that backup files and history is only
    deleted when a backup is successful. What I don’t like is the use of xp_cmdshell and I would love to see an elegant solution on how to delete files without using xp_cmdshell.

    /* Cleaning MarkLog Tables if entry is older than @DaysToKeep x 10
    */

    DECLARE @cmd2 nvarchar(MAX)
    DECLARE BackupDatabases CURSOR
    FOR SELECT ‘[‘+ServerName+’].[‘+DatabaseName+’]’ FROM admv_BackupDatabases

    OPEN BackupDatabases
    FETCH NEXT FROM BackupDatabases INTO @cmd2
    WHILE (@@fetch_status -1)
    BEGIN
    IF (@@fetch_status -2)
    BEGIN
    SET @cmd2 = ‘DELETE FROM ‘+@cmd2 + ‘.[dbo].[MarkLog] WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),”_”,””), GETDATE()) >’ + Convert(varchar,@DaysToKeep) + ‘0’
    EXEC sp_executesql @cmd2
    END
    FETCH NEXT FROM BackupDatabases INTO @cmd2
    END
    CLOSE BackupDatabases
    DEALLOCATE BackupDatabases

    Best Regards,
    Jimmy H

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s