none
MessageBox log file size RRS feed

  • Question

  • Hi, 

    In our prod environment, the MessageBox data file is withing the recommended limits - 2GB, but the log file is 32GB. Is this a reason to worry, or it is normal?  I couldn't find any recommendations on this. 

    Thank you very much!

    Friday, July 18, 2014 10:10 PM

Answers

  • It happens mainly when your DTA Archive and Purge Job does not run for certain interval of time and log size  get increased  .

    What you need to do is to Shrink the Message DB to have the free space on the drive. The recommended 5GB include your Data file +SQL log file .

    you can do it by 2 ways

    First one include T-SQL

    USE master
    ALTER DATABASE BizTalkMgmtDb SET RECOVERY SIMPLE WITH NO_WAIT
    
    Use BizTalkMsgBoxDb
    DBCC SHRINKFILE (N'BizTalkMsgBoxDb_log' , 0, TRUNCATEONLY)
    GO
    
    USE master
    ALTER DATABASE BizTalkMsgBoxDb SET RECOVERY FULL WITH NO_WAIT
    GO

    While second one through SQL Management Studio

    right click on the BizTalkMsgDb database, click on Tasks > Shrink > Database

    Note : It is highly recommended to check the status of your DTA Archive and Purge Job so please double check state and size of DTA DB.

    configure the Backup BizTalk job to stop unchecked log file growth. The backup job will make sure that log files don't grow out of control and clear away the backup files created  on regular interval

    http://blogical.se/blogs/johan/archive/2009/11/10/cleaning-up-biztalk-database-backups.aspx

    Thanks

    Abhishek


    Saturday, July 19, 2014 2:19 AM
  • This is not normal.

    IMO 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 using following SQL command

    USE BiztalkMsgBoxDb;
    
    GO
    
    -- Truncate the log by changing the database recovery model to SIMPLE.
    
    ALTER DATABASE BiztalkMsgBoxDb
    
    SET RECOVERY SIMPLE;
    
    GO
    
    -- Shrink the truncated log file to 1 MB.
    
    DBCC SHRINKFILE (BiztalkMsgBoxDb_Log, 2);
    
    GO
    
     

    I would recommend you to have a read of following articles

    BizTalk Environment Maintenance from a DBA perspective 

    BizTalk Databases: Survival Guide

    hope this helps. 


    Greetings,HTH
    Naushad Alam

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
    alamnaushad.wordpress.com

    • Marked as answer by Angie Xu Thursday, July 24, 2014 5:58 AM
    Friday, July 18, 2014 10:31 PM
    Moderator
  • My question still remains - what's considered the normal log file size? Documentation states that the MessageBox database should be within 5GB. These 5GB - is this the data file size only, or the data file size + the log file size?

    There is no 'normal' log file size as it depends entirely on the Messaging and Orchestration profile of your applications.  High volume, large messages, Orchestration persistence all result in much more data to log than a simple pub/sub app.

    So, there is only a normal size for your applications.  The important thing is that it does not grow uncontrolled which the SQL jobs take care of.

    Also, the same goes for the MessageBox size.  Over 5GB is not necessarily a problem if you applications are legitimately persisting that data.

    • Marked as answer by Angie Xu Thursday, July 24, 2014 5:58 AM
    Saturday, July 19, 2014 11:16 AM
    Moderator

All replies

  • This is not normal.

    IMO 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 using following SQL command

    USE BiztalkMsgBoxDb;
    
    GO
    
    -- Truncate the log by changing the database recovery model to SIMPLE.
    
    ALTER DATABASE BiztalkMsgBoxDb
    
    SET RECOVERY SIMPLE;
    
    GO
    
    -- Shrink the truncated log file to 1 MB.
    
    DBCC SHRINKFILE (BiztalkMsgBoxDb_Log, 2);
    
    GO
    
     

    I would recommend you to have a read of following articles

    BizTalk Environment Maintenance from a DBA perspective 

    BizTalk Databases: Survival Guide

    hope this helps. 


    Greetings,HTH
    Naushad Alam

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
    alamnaushad.wordpress.com

    • Marked as answer by Angie Xu Thursday, July 24, 2014 5:58 AM
    Friday, July 18, 2014 10:31 PM
    Moderator
  • Thank you, Naushad!

    The jobs have been configured, but I definitely will double check.

    My question still remains - what's considered the normal log file size? Documentation states that the MessageBox database should be within 5GB. These 5GB - is this the data file size only, or the data file size + the log file size?

    Thank you, Sergei

    Friday, July 18, 2014 11:01 PM
  • It happens mainly when your DTA Archive and Purge Job does not run for certain interval of time and log size  get increased  .

    What you need to do is to Shrink the Message DB to have the free space on the drive. The recommended 5GB include your Data file +SQL log file .

    you can do it by 2 ways

    First one include T-SQL

    USE master
    ALTER DATABASE BizTalkMgmtDb SET RECOVERY SIMPLE WITH NO_WAIT
    
    Use BizTalkMsgBoxDb
    DBCC SHRINKFILE (N'BizTalkMsgBoxDb_log' , 0, TRUNCATEONLY)
    GO
    
    USE master
    ALTER DATABASE BizTalkMsgBoxDb SET RECOVERY FULL WITH NO_WAIT
    GO

    While second one through SQL Management Studio

    right click on the BizTalkMsgDb database, click on Tasks > Shrink > Database

    Note : It is highly recommended to check the status of your DTA Archive and Purge Job so please double check state and size of DTA DB.

    configure the Backup BizTalk job to stop unchecked log file growth. The backup job will make sure that log files don't grow out of control and clear away the backup files created  on regular interval

    http://blogical.se/blogs/johan/archive/2009/11/10/cleaning-up-biztalk-database-backups.aspx

    Thanks

    Abhishek


    Saturday, July 19, 2014 2:19 AM
  • My question still remains - what's considered the normal log file size? Documentation states that the MessageBox database should be within 5GB. These 5GB - is this the data file size only, or the data file size + the log file size?

    There is no 'normal' log file size as it depends entirely on the Messaging and Orchestration profile of your applications.  High volume, large messages, Orchestration persistence all result in much more data to log than a simple pub/sub app.

    So, there is only a normal size for your applications.  The important thing is that it does not grow uncontrolled which the SQL jobs take care of.

    Also, the same goes for the MessageBox size.  Over 5GB is not necessarily a problem if you applications are legitimately persisting that data.

    • Marked as answer by Angie Xu Thursday, July 24, 2014 5:58 AM
    Saturday, July 19, 2014 11:16 AM
    Moderator
  • Thank you everybody!
    Sunday, July 20, 2014 4:01 AM