New release: Managing and cleaning BizTalk Server MarkLog database tables: sp_DeleteBackupHistoryAndMarkLogsHistory

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

Almost three weeks ago in my last post I release a SQL script to manager 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 beautiful of the BizTalk Community is that sometimes they provided 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 that I had the pleasure of meeting him in person, I think the first time was two years ago in Norway, describing this exact limitations but… you are making a mistake if you think that he only described the limitations!!

Indeed he spent some time describing the limitations but I also provided me with an updated scripts with all the improvement necessary to suppress these limitations! Basically Mikael updated the script using an identical logic from another SP in the backup job so that 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 is that:

  • Instead of hard coding implement all the delete necessary 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 have implementing this same operation dynamically by “stolen” the cursors logic and realservername from the BizTalk procedure “sp_MarkBTSLogs”. The cursor iterates 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 implemented some kind of error handling, just in case of something fails that an exception will be raised, as you can see in this example picture bellow:

sp_DeleteBackupHistoryAndMarkLogsHistory-error-handling

Special thanks for 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.

You can download the last version of 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. Hugo says:

    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

  2. Hugo says:

    Hi Sandro,
    BizTalk 2013 R2 Enterprise and SQL 2014 Enterprise.
    Thanks,
    Hugo

  3. Hugo says:

    Hi Sandro,
    Do you maybe know what I can do to fix this issue? It would be great to have this running.
    Thanks,
    Hugo

  4. Hugo says:

    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

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