locked
BT Prod Database Size Growing RRS feed

  • Question

  • Hi,

    It seems that most of Biztalk databases are growing and do not reduce in size, by themselves.

    At the moment the

    MsgboxDB is 2582.75 MB

    MgmtDb is 1759.25 MB

    DTAdb is 3715.69 MB

    SSOdb  is 681.44 MB

    All the biztalk jobs seem to run okay but maybe there is another source of the problem. Kindly sugget. Thanks.

    //Ankur


    Ankur Seth

    Monday, May 28, 2012 12:55 PM

Answers

  • Hi Ankur,

    The size of the BizTalk Server Messagebox database should typically be no more than approximately 5GB (Your is approximately 2.5 Gb). An environment with a powerful SQL Server backend and numerous long running orchestrations may have a BizTalkMsgBoxDb database larger than 5GB. A high volume environment with no long-running orchestrations should have a BizTalk Server Messagebox database much smaller than 5GB. The BizTalk Server tracking database can vary greatly in size but if query performance decreases dramatically, then the tracking database is probably too large. As a rule of thumb, a BizTalk Server tracking database larger that 15-20 GB is considered too large and may adversely impact performance! Are you experiencing any performance issues regarding the size of the MessageBox? Or are you experiencing the following issues due to fact that the BizTalk Server databases that are (too) large:

    • The BizTalk Server Messagebox database continues to grow while the data size (not just the log file) remains large.
    • BizTalk Server takes a longer time than normal to process even a simple message flow scenario.
    • Health and Activity Tracking (HAT) queries take a longer time than normal and may even timeout.
    • The database log file never gets truncated.
    • The BizTalk SQL Agent jobs run slower than normal.
    • Some tables are considerably large or have too many rows compared to normal.

    The BizTalk Server databases can become large for several reasons including:

    • BizTalk SQL Agent Jobs not running (you can rule this out as the jobs are running)
    • Excessive suspended message or service instances
    • Disk failures (you can investigate that i.e. event log or checkdisk command line)
    • High levels of tracking (you can investigate that)
    • BizTalk Server throttling (event log)
    • Poor SQL Server performance (see for instance the checklist Maintaining and Troubleshooting BizTalk Server Databases)
    • Network latency issues (you can investigate that)

    You can use BizTalk Best Practice Analyzer or BizTalk MessageBox Viewer to see if there are issue's in this regard.

    Update: I also created a blog post regarding this thread/quesion/scenario and a script to verify if BizTalk database jobs are running.

    HTH

    Steef-Jan Wiggers

    MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ |@SteefJan

    If this answers your question please mark it accordingly

    BizTalk Server 2010 Cookbook


    BizTalk


    Monday, May 28, 2012 1:49 PM
    Moderator
  • Hi Ankur,

    What is the status of your BizTalk Server Jobs on SQL Server? Please check them , and their logs, if their running fine.

    Please have a look of this wiki BizTalk Databases: Survival Guide by Steef-Jan to further start resolving things around BizTalk Databases.


    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
    My new TechNet Wiki "BizTalk Server: Performance Tuning & Optimization"

    Monday, May 28, 2012 1:23 PM
    Moderator

All replies

  • Hi Ankur,

    What is the status of your BizTalk Server Jobs on SQL Server? Please check them , and their logs, if their running fine.

    Please have a look of this wiki BizTalk Databases: Survival Guide by Steef-Jan to further start resolving things around BizTalk Databases.


    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
    My new TechNet Wiki "BizTalk Server: Performance Tuning & Optimization"

    Monday, May 28, 2012 1:23 PM
    Moderator
  • Hi Ankur,

    The size of the BizTalk Server Messagebox database should typically be no more than approximately 5GB (Your is approximately 2.5 Gb). An environment with a powerful SQL Server backend and numerous long running orchestrations may have a BizTalkMsgBoxDb database larger than 5GB. A high volume environment with no long-running orchestrations should have a BizTalk Server Messagebox database much smaller than 5GB. The BizTalk Server tracking database can vary greatly in size but if query performance decreases dramatically, then the tracking database is probably too large. As a rule of thumb, a BizTalk Server tracking database larger that 15-20 GB is considered too large and may adversely impact performance! Are you experiencing any performance issues regarding the size of the MessageBox? Or are you experiencing the following issues due to fact that the BizTalk Server databases that are (too) large:

    • The BizTalk Server Messagebox database continues to grow while the data size (not just the log file) remains large.
    • BizTalk Server takes a longer time than normal to process even a simple message flow scenario.
    • Health and Activity Tracking (HAT) queries take a longer time than normal and may even timeout.
    • The database log file never gets truncated.
    • The BizTalk SQL Agent jobs run slower than normal.
    • Some tables are considerably large or have too many rows compared to normal.

    The BizTalk Server databases can become large for several reasons including:

    • BizTalk SQL Agent Jobs not running (you can rule this out as the jobs are running)
    • Excessive suspended message or service instances
    • Disk failures (you can investigate that i.e. event log or checkdisk command line)
    • High levels of tracking (you can investigate that)
    • BizTalk Server throttling (event log)
    • Poor SQL Server performance (see for instance the checklist Maintaining and Troubleshooting BizTalk Server Databases)
    • Network latency issues (you can investigate that)

    You can use BizTalk Best Practice Analyzer or BizTalk MessageBox Viewer to see if there are issue's in this regard.

    Update: I also created a blog post regarding this thread/quesion/scenario and a script to verify if BizTalk database jobs are running.

    HTH

    Steef-Jan Wiggers

    MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ |@SteefJan

    If this answers your question please mark it accordingly

    BizTalk Server 2010 Cookbook


    BizTalk


    Monday, May 28, 2012 1:49 PM
    Moderator
  • Hi,

    Thanks all for sharing good links. The content in the links was very good.

    Coming back to my problem, I checked the performance of hosted applications which is also good. The only way I think is that we have some 1000 suspended messages.

    To me this is high count considering we don't have many long running orchestrations. So in this case, is 2.5 GB size expected?


    Ankur Seth

    Tuesday, May 29, 2012 11:09 AM
  • hi Ankur,

    I am hoping you already know this, but putting this point upfront, just in case.The databases grow in size and do not come down as and when your messages are cleared. The amount of free space "inside" this DB file gets available. So, the DB file might show you 2.5GB.. but in actual sense only 1GB might be actually getting used. If you need to bring the size down, you can use the DBCC SHRINK command.. but we usually leave the size to a particular level say 2GB.. so that your SQL server "already" has all the space it needs upfront.


    Praveen Behara
    MCST : BizTalk Server 2006 R2

    Tuesday, May 29, 2012 7:52 PM