Almost three weeks ago, in my last post I release a SQL script to manage and delete the unnecessary records in the BizTalk “MarkLog” tables according to some of the best practices (you can read more here: Managing and cleaning BizTalk Server MarkLog database tables according to some Best Practices), however, I knew in advance that the script had some limitations:
- Basically, the user needs to update the script manually after “installing” it. He/she needs to add and remove the databases that might not be in use (like BAM).
- Or add, if more than one MsgBox is used.
But the beauty of the BizTalk Community is that sometimes they provide awesome feedback, and that was exactly what happened in this case. 10 days after I published my post, I received an email from Mikael Sand, Mikael is a strong BizTalk Community member from Sweden, whom I had the pleasure of meeting in person, I think the first time was two years ago in Norway, describing these exact limitations, but… You are making a mistake if you think that he only described the limitations!!
📝 One-Minute Brief
This post introduces a script (sp_DeleteBackupHistoryAndMarkLogsHistory) designed to help BizTalk administrators manage the growth of the MarkLog database. The script removes old backup and MarkLog history records that accumulate over time and can cause storage usage and performance issues. By scheduling this cleanup alongside regular BizTalk maintenance jobs, administrators can keep the environment stable and prevent database bloat. (blog.sandro-pereira.com
)
Indeed, he spent some time describing the limitations, but he also provided me with updated scripts with all the improvements necessary to suppress these limitations! Basically, Mikael updated the script using an identical logic from another SP in the backup job so that the user doesn’t need to update the script once it is installed. Also, it does not need to be updated if a database (like BAM) is added later or if you are using more than one MsgBox.
Basically, the differences between the first release and this one are that:
- Instead of hard-coding, implement all the necessary delete queries:
/****** 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
- Mikael has implemented this same operation dynamically by “stealing” the cursor’s logic and real server names from the BizTalk procedure sp_MarkBTSLogs. The cursor iterates over all the databases that are backed up by BizTalk.
DECLARE @BackupServer sysname, @BackupDB sysname, @RealServerName sysname
DECLARE @tsql nvarchar(1024)
DECLARE @ret int
/* Create a cursor */
DECLARE BackupDB_Cursor insensitive cursor for
SELECT ServerName, DatabaseName
FROM admv_BackupDatabases
ORDER BY ServerName
open BackupDB_Cursor
fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Get the proper server name
EXEC @ret = sp_GetRemoteServerName @ServerName = @BackupServer, @DatabaseName = @BackupDB, @RemoteServerName = @RealServerName OUTPUT
/* Create the delete statement */
select @tsql =
'DELETE FROM [' + @RealServerName + '].[' + @BackupDB + '].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),''_'',''''), GETDATE()) > ' + cast(@DaysToKeep as nvarchar(5) )
/* Execute the delete statement */
exec (@tsql)
/* Get the next DB. */
fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
END
close BackupDB_Cursor
deallocate BackupDB_Cursor
Then I spent some days testing and improving a little more the script, and again by looking to the same SP: sp_MarkBTSLogs, I was able to implement some kind of error handling, just in case something fails, an exception will be raised, as you can see in this example picture below:
Special thanks to Mikael Sand for the feedback and for this new implementation logic, and of course, to the previous persons involved: Tord Glad Nordahl, Rui Romano, and Pedro Sousa.
Download
THIS SQL 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:
Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son.
Hi Sandro,
Thanks for the script. It always felt like a bit much to shutdown the environment and run terminator to clean a table.
I have one problem with running the procedure. I keep getting :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
I first created the procedure by running v1.2 of your script then ran v2 to update it,
Do you know what is causing this problem?
Thanks,
Hugo
Hi Hugo,
What is the BizTalk Version and SQL version that you are using?
in the backup job, in the step named MarkAndBackupLog You
have probably set a mark name (first parameter) that has more or less
than 3 characters. You will need to modify the procedure (in line 75,
first argument of SUBSTRING should be equal to Your markname length+2)
Hi Sandro,
BizTalk 2013 R2 Enterprise and SQL 2014 Enterprise.
Thanks,
Hugo
Hi Sandro,
Do you maybe know what I can do to fix this issue? It would be great to have this running.
Thanks,
Hugo
Hi Sandro,
I think I fixed it by modifying this in your procedure:
/* Create the delete statement */
select @tsql =
‘DELETE FROM [‘ + @RealServerName + ‘].[‘ + @BackupDB + ‘].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],10,10),”_”,”-”), CONVERT (date, GETDATE())) > ‘ + cast(@DaysToKeep as nvarchar(5) )
Now it deletes the records and I no longer get any errors.
Regards,
Hugo