none
BizTalk Management DB Log File Size RRS feed

  • Question

  • Hi,

    We have been recently been doing some performance testing on our system, and when running one of our service requests approximately 40 000 times the log file has been growing extremely large. The backup last night ended up at 160 GB. We have been running various cleanup scripts but this hasn't helped with the backup size. 

    The service has approximately 26 message box hits. I've been developing in BizTalk for some time but have little experience with the database side of things. I'm unsure if there is any specific logging that I should be disabling but as this is related to the SQL log, I'm not sure if there are any BizTalk settings I can change.

    I have disable all tracking except for the Group Tracking setting on the host configuration. 

    Any thoughts would be appreciated.

    Tuesday, September 1, 2015 11:22 AM

Answers

  • Very important question: Are you asking about the Management database of the MessageBox database?

    Also, you mean 26 MessageBox hits per invocation?

    Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

    Yes, approximately 26 message box invocations per request.


    Step-by-step:

    1. Verify there is at least one running instance of a Tracking Host.

    2. Verify all the SQL Jobs are configured (DTA Purge And Archive and Backup BizTalk Server require configuration) and runnign without error.

    Also run BizTalk MessageBox Viewer to capture errors and it would suggest you the possible causes and its solutions. If MessageBox Viewer identifies this issue is due to Messages without reference counts, it would suggest you to run the terminator tool.


    Also if all jobs are running as intended without any warnings/errors in the SQL Server event log, your backup job is configured, and you don't have many running/suspended instances, then I would assume that the files has grown because there used to be a problem that is now resolved.

    In that case I would stop my BizTalk host instance(s) and shrink both the MessageBox data file and log file, by running the following:

    Use
    BizTalkMsgBoxDb
    DBCC SHRINKFILE (N'BizTalkMsgBoxDb' , 0, TRUNCATEONLY)
    GO
    
    
    DBCC SHRINKFILE (N'BizTalkMsgBoxDb_log' , 0, TRUNCATEONLY)
    GO


    Please mark as answer or vote as helpful if my reply does



    • Edited by Rachit SikroriaModerator Wednesday, September 2, 2015 6:51 AM
    • Proposed as answer by Angie Xu Monday, September 7, 2015 3:07 AM
    • Marked as answer by Angie Xu Tuesday, September 8, 2015 12:26 PM
    Wednesday, September 2, 2015 6:40 AM
    Moderator
  • Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

    Yes, approximately 26 message box invocations per request.


    If the log file continues to grow, then the Backup BizTalk Server Agent Job is not configured or running.

    1. Configure the Backup BizTalk Server job.
    2. Ensure the job runs successfully.

    Instructions: https://msdn.microsoft.com/en-us/library/aa546765.aspx

    After at least one successful backup, you will have to manually shrink the files.

    • Proposed as answer by Angie Xu Monday, September 7, 2015 3:07 AM
    • Marked as answer by Angie Xu Tuesday, September 8, 2015 12:26 PM
    Wednesday, September 2, 2015 10:49 AM
    Moderator
  • Very important question: Are you asking about the Management database of the MessageBox database?

    Also, you mean 26 MessageBox hits per invocation?

    Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

    Yes, approximately 26 message box invocations per request.


    Nolan,

    I have already faced the same issue. I would suggest to please look into below article, that will help to resolve your issue.

    Issue: Size of BizTalk tracking databases grows rapidly and causes poor disk performance or fills up the disk subsystem.


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    • Proposed as answer by Angie Xu Monday, September 7, 2015 3:07 AM
    • Marked as answer by Angie Xu Tuesday, September 8, 2015 12:26 PM
    Wednesday, September 2, 2015 6:54 AM
    Moderator

All replies

  • Hi Nolan,

    This is not normal.

    Seems your BizTalk database Jobs are not running , Make sure your BizTalk SQL servers jobs have been enabled and SQL server agent is running. 

    Please have a look of How to Configure the Backup BizTalk Server Job article to enable the jobs

    The BizTalk backup job is responsible for keeping the log file size in the limit. 

    you can try shrinking the log file. Please have a look on related thread

    MessageBox log file size

    Please follow below useful article for reference,

    Performance issues in BizTalk and How to maintain and troubleshoot BizTalk Server databases

    BizTalk Databases: Survival Guide


    Thanks, If my reply is helpful please mark as answer or vote as helpful.


    Tuesday, September 1, 2015 11:54 AM
    Moderator
  • Very important question: Are you asking about the Management database of the MessageBox database?

    Also, you mean 26 MessageBox hits per invocation?

    Tuesday, September 1, 2015 11:59 AM
    Moderator
  • Hi,

    There are quite a few jobs that runs in BizTalk DB to keep it clean and als takes regular backup of the DB. You need to check which DB (MsgBox or ManagmentDB) is getting large.

    The link below gives the details of the Jobs that you need to check if it is running fine on your environment:

    SQL Server Agent jobs in BizTalk Server

    Specially look for the following jobs in your BizTalk DB environment:

    • MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb
    • Backup BizTalk Server

    Rahul

    Tuesday, September 1, 2015 12:59 PM
  • Hi,

    If you are referencing MessageBox hits, there are transaction records which could be the major cause for high memory consumption. As mentioned by experts you will have to be careful with the purges and have scheduled cleanups in production, your admin has do that anyways. 

    If you are referring to development, take a simple route of shrinking the database. ONLY DEVELOPMENT NOT PRODUCTION

    use [DatabaseName]
    go
    BACKUP LOG [DatabaseName] TO DISK='NUL'
    dbcc shrinkfile([DatabaseName_LogfileName]_log, truncateonly)
    go
    

    Also take a backup before you perform the shrink. 

    K

    Tuesday, September 1, 2015 1:31 PM
  • Very important question: Are you asking about the Management database of the MessageBox database?

    Also, you mean 26 MessageBox hits per invocation?

    Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

    Yes, approximately 26 message box invocations per request.


    Wednesday, September 2, 2015 6:11 AM
  • Very important question: Are you asking about the Management database of the MessageBox database?

    Also, you mean 26 MessageBox hits per invocation?

    Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

    Yes, approximately 26 message box invocations per request.


    Step-by-step:

    1. Verify there is at least one running instance of a Tracking Host.

    2. Verify all the SQL Jobs are configured (DTA Purge And Archive and Backup BizTalk Server require configuration) and runnign without error.

    Also run BizTalk MessageBox Viewer to capture errors and it would suggest you the possible causes and its solutions. If MessageBox Viewer identifies this issue is due to Messages without reference counts, it would suggest you to run the terminator tool.


    Also if all jobs are running as intended without any warnings/errors in the SQL Server event log, your backup job is configured, and you don't have many running/suspended instances, then I would assume that the files has grown because there used to be a problem that is now resolved.

    In that case I would stop my BizTalk host instance(s) and shrink both the MessageBox data file and log file, by running the following:

    Use
    BizTalkMsgBoxDb
    DBCC SHRINKFILE (N'BizTalkMsgBoxDb' , 0, TRUNCATEONLY)
    GO
    
    
    DBCC SHRINKFILE (N'BizTalkMsgBoxDb_log' , 0, TRUNCATEONLY)
    GO


    Please mark as answer or vote as helpful if my reply does



    • Edited by Rachit SikroriaModerator Wednesday, September 2, 2015 6:51 AM
    • Proposed as answer by Angie Xu Monday, September 7, 2015 3:07 AM
    • Marked as answer by Angie Xu Tuesday, September 8, 2015 12:26 PM
    Wednesday, September 2, 2015 6:40 AM
    Moderator
  • Very important question: Are you asking about the Management database of the MessageBox database?

    Also, you mean 26 MessageBox hits per invocation?

    Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

    Yes, approximately 26 message box invocations per request.


    Nolan,

    I have already faced the same issue. I would suggest to please look into below article, that will help to resolve your issue.

    Issue: Size of BizTalk tracking databases grows rapidly and causes poor disk performance or fills up the disk subsystem.


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    • Proposed as answer by Angie Xu Monday, September 7, 2015 3:07 AM
    • Marked as answer by Angie Xu Tuesday, September 8, 2015 12:26 PM
    Wednesday, September 2, 2015 6:54 AM
    Moderator
  • Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

    Yes, approximately 26 message box invocations per request.


    If the log file continues to grow, then the Backup BizTalk Server Agent Job is not configured or running.

    1. Configure the Backup BizTalk Server job.
    2. Ensure the job runs successfully.

    Instructions: https://msdn.microsoft.com/en-us/library/aa546765.aspx

    After at least one successful backup, you will have to manually shrink the files.

    • Proposed as answer by Angie Xu Monday, September 7, 2015 3:07 AM
    • Marked as answer by Angie Xu Tuesday, September 8, 2015 12:26 PM
    Wednesday, September 2, 2015 10:49 AM
    Moderator