Monitor biztalk server(biztalkmgmtdb) sql agent jo

Question

In BizTalk Server 2013 R2 there's a job called Monitor BizTalk Server (BizTalkMgmtDb). This job checks the management and tracking databases and looks for any inconsistencies. If there are none then the job completes successfully. If there are inconsistencies, then the job fails.

The job failing is simply telling you that you must take care of the inconsistencies.

Error message:

Executed as user: AD-ENT\DBA_LIFTS_SQL_SSDE_N. 67981 Orphaned DTA Service Instances in WUPWD02V0310\BTBAMUAT.BizTalkDTADb [SQLSTATE 42000] (Error 50000). The step failed.

    To find inconsistencies follow below procedure from SQL instance:

            Ø Step-1: Run below query from SQL instance 

            CREATE VIEW  [dbo].[bts_Monitor_Job_Issues]
            AS
                SELECT Inconsistancy.DBServer AS [Server Name], Inconsistancy.DBName AS  [Database  Name], Inconsistancy.nProblemCode AS [Problem Code], Issue.nvcProblemDescription AS [Description] FROM btsmon_Inconsistancies Inconsistancy INNER JOIN [dbo].[btsmon_Issues] Issue ON  Inconsistancy.nProblemCode = Issue.nProblemCode
            WHERE Inconsistancy.nCount > 0
            GO

            Ø Step-2: 

            USE [BizTalkMgmtDb]
            GO
             
            SELECT [Server Name]
                  ,[Database  Name]
                  ,[Problem Code]
                  ,[Description]
              FROM [dbo].[bts_Monitor_Job_Issues]
            GO

To fix the issue simply run BizTalk Terminator also called BizTalk Health Monitor (BHM). 1.To fix "messages with refcount less than 0" error run Repair Refcounts for All Messages in BTS Terminator. 2.To fix "Orphaned DTA service instances" error Run repair Orphaned DTA service instances 3.To fix "orphaned DTA service instances" error run DELETE Orphaned DTA Service Instance Expections in BTS Terminator.

Does any one know about how can removed biztalk messages from messageboxdb using sql query?

I can use biztalk terminators tool to remove those type of message from db but I wonder to know how it's possible using sql query please if you have answer that would be great!

Answer 1

Using BizTalk Terminator or BizTalk Health Monitor will, most likely, fix your issues.

If you want to know more about the queries that are running in the background while fixing the issues, you could do as Dijkgraaf mentions in the comments: run something like SQL profiler to check which database queries are executed. Although this might violate some EULA, you might even go as far as decompiling binaries to understand and see exactly what happens.

Note: be aware that you need to stop all BizTalk hosts instances before running these invasive queries.