none
Has anybody have idea what is updatemsgbox_Assemble stored procedure used for? RRS feed

  • Question

  • Has anybody have idea what is updatemsgbox_Assemble stored procedure used for?

    Every weekend we are getting deadlock errors as show below followed by database connectivity loose 

    The following stored procedure call failed: " { call [dbo].[bts_UpdateMsgbox_Assemble]( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}". SQL Server returned error string: "Transaction (Process ID 992) was deadlocked on lock | thread resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

     

    An error occurred that requires the BizTalk service to terminate. The most common causes are the following:
     1) An unexpected out of memory error.
     OR
     2) An inability to connect or a loss of connectivity to one of the BizTalk databases. 
     The service will shutdown and auto-restart in 1 minute. If the problematic database remains unavailable, this cycle will repeat.

     Error message: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
     Error source: .Net SqlClient Data Provider 

     All receive locations are being temporarily disabled because either the MessageBox or Configuration database is not available. When these databases become available, the receive locations will be automatically enabled.

    We are facing this every weekend,

    to resolve this we r doing work around like 

    stop all hosts

    start orchestration n send host

    start receive host and its receivelocation batach wise 

    I have also noticed we have some failover cluster host instances which are in active-passive when ever this thing happens they change the node(cluster Biztalk environment(2 servers)).please help me to sort out ASAP 

    Friday, September 14, 2018 8:35 AM

All replies

  • You will have a bts_UpdateMsgBox stored procedure for each host instance 

    You can trace what is causing the deadlock either by using DBCC 1222 flag or the profiler
    Please check this post
    https://social.msdn.microsoft.com/Forums/en-US/5886a071-1b26-4dea-9f91-a5d76aeec293/btsupdatemsgbox-error-with-deadlocks?forum=biztalkgeneral

    You can also search for "bts_UpdateMsgBox" on Google

    Probably cause is bad design in your implementation

    hth /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Friday, September 14, 2018 9:00 AM
  •  All receive locations are being temporarily disabled because either the MessageBox or Configuration database is not available. When these databases become available, the receive locations will be automatically enabled.

    We are facing this every weekend,

    to resolve this we r doing work around like 

    stop all hosts

    start orchestration n send host

    start receive host and its receivelocation batach wise 

    I have also noticed we have some failover cluster host instances which are in active-passive when ever this thing happens they change the node(cluster Biztalk environment(2 servers)).please help me to sort out ASAP 

    Check with your infra, network and DBA teams - something is causing the SQL connection from the BizTalk nodes to fail over the weekend. This is causing the Host Instances to restart and the cluster services to failover. They should be able to help.

    Thanks Arindam

    Friday, September 14, 2018 11:25 AM
    Moderator
  • Thanks Peter for you suggestion,

    My Biztalk version is 2016

    Max im getting these errors from Saturday night to monday morning untill we restart biztalk and sql services and enabling the related receive location in batch wise.

    And we observed mostly the timings we get these errors are 12 cet, 2.30 cet and 5.30.

    In my current project the index job runs while messages processing only, Client not agreeing to stop host instances to run index job it schedule at 1 cet every Saturday. but we use get dead lock and db disconnection some times before 1 cet like 11.30 cet or 12 cet.

    I hope this errors not because of index job execution

     



    • Edited by Phani_B Sunday, September 16, 2018 3:21 PM
    Sunday, September 16, 2018 2:49 PM
  • Hi peter,

    I have found all those dead locks occur to one store procedure bts_UpdateMsgbox_Assemble.

    Assemble one of our host instance . Assemble is a orchestration host instance .There is only orchestration with assemble host is available  

    i.e this orchestration causing issue ?

    Is this because bad design of that orchestration?

    And No-ref message count also increasing from Saturday with high volume  

    On other hand I also notices ENTSSO and Db disconnection warning without deadlocks for a minute is that big issue?

    But when dead lock errors logs the issue last for 10 minutes to 1 hour(if we noticed deadlocks for one hour we restart all biztalk n SQL services).


    • Edited by Phani_B Sunday, September 16, 2018 4:11 PM
    Sunday, September 16, 2018 3:56 PM
  • How is the indexes being rebuild? 
    BTS needs to be taken offline before rebuildng in any case

    .. the BizTalk Server databases should never be rebuilt while BizTalk is processing data.

    Checklist: Maintaining and Troubleshooting BizTalk Server Databases
    https://docs.microsoft.com/en-us/biztalk/technical-guides/checklist-maintaining-and-troubleshooting-biztalk-server-databases

    Check the above article for the reason

    The blocking and deadlocks are most likely caused by reindexing when BTS is online

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Sunday, September 16, 2018 6:39 PM
  • If these errors happen only in the weekends the reindexing might be the root cause

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Sunday, September 16, 2018 6:41 PM

  • In my current project the index job runs while messages processing only, Client not agreeing to stop host instances to run index job it schedule at 1 cet every Saturday. but we use get dead lock and db disconnection some times before 1 cet like 11.30 cet or 12 cet.

    I hope this errors not because of index job execution



    That is the problem. You cannot re-index the databases without a downtime. BizTalk databases are bit of a different beast compared to any other app database, and a DBA needs to understand/follow recommendations from MSFT-
    https://support.microsoft.com/en-us/help/952555/how-to-maintain-and-troubleshoot-biztalk-server-databases

    Thanks Arindam

    Sunday, September 16, 2018 11:32 PM
    Moderator
  • But we have one old Database which having data from long years, our index job taking at least 3 hours some time more than that.

    we can't keep the BT down upto that time :(

    Monday, September 17, 2018 5:51 AM
  • Most Biztalk database are transactional databases and should not contain any old information / be kept lean

    Are all your BTS SQL Server Agent Jobs running without errors?
    Data needs to be purged from BAM and DTA

    What is the size of your databases?

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Monday, September 17, 2018 6:13 AM
  • But we have one old Database which having data from long years, our index job taking at least 3 hours some time more than that.

    we can't keep the BT down upto that time :(


    Which database is that? Most of the BizTalk database sizes should be kept in check by the out-of-the-box BizTalk SQL Agent Jobs, apart from BAM.

    Thanks Arindam

    Monday, September 17, 2018 6:37 AM
    Moderator
  • All jobs running as expected only

    Our index job runs every Saturday 1 cet but issue coming during Saturday night    

    Monday, September 17, 2018 8:42 AM
  • What is the size of your databases?

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Monday, September 17, 2018 8:48 AM
  • DTA DB

    size -54GB available space 36GB

    MsgBoxDB

    Size-70GB available 38GB

    MgmtDB

    Size 720MB available 109MB

    SSODB

    Size-144MB available space 16.29MB

    in addition to Biztalk database we have few custom DB's.

    Monday, September 17, 2018 12:02 PM
  • As stated here, you should not rebuild the indexes on BizTalk DBs when BizTalk is online. Other custom DBs can be reindexed.
    However, the thing to first check is if the time when the errors start correlate with when the reindex job was run. If not, check what else was happened around that time, was there heavy load in BizTalk, was some other activity happening on SQL?

    Thanks Arindam

    Monday, September 17, 2018 12:24 PM
    Moderator
  • Your MsgBoxDb and DtaDb are very large 

    Do you have a large amount of data running through the system?
    If transaction log files are smaller than the database size you probably have an issue here

    Do you have a dedicated host enabled/running for tracking?
    This will move tracking data from MsgBoxDB to the DTADb

    How long do you keep tracking data?

    Please check that the SQL Server Agent are configured according to best practices

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Monday, September 17, 2018 12:33 PM