title: Monitor biztalk server(biztalkmgmtdb) sql agent job failed on step 2 - Part1
body: <p>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.</p>
  
  <p>The job failing is simply telling you that you must take care of the inconsistencies.</p>
  
  <p>Error message: </p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p><a href="https://i.stack.imgur.com/uoaes.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uoaes.png" alt="Monitor BizTal Server(BizTalkMgmtDb)"></a></p>
  
  <pre><code>    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 &gt; 0
              GO
  
              Ø Step-2: 
  
              USE [BizTalkMgmtDb]
              GO
               
              SELECT [Server Name]
                    ,[Database  Name]
                    ,[Problem Code]
                    ,[Description]
                FROM [dbo].[bts_Monitor_Job_Issues]
              GO
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/vSBaJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vSBaJ.png" alt="Sql query"></a></p>
  
  <p>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.</p>
  
  <p>Does any one know about how can removed biztalk messages from messageboxdb using sql query? </p>
  
  <p>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!</p>