Biztalk database size RRS feed

  • Question

  • Hi

    We are getting following warning in production 

    database throttling  warning  event viewer  with  Code [7001].

    BizTalk host BizTalkServerBatchReceiveHost throttled because DatabaseSize exceeded the configured throttling limit.

    When we check DB size they are 



    29706.3 MB



    379.438 MB



    3962 MB



    3483.56 MB



    5096 MB

    What are the best options to purge this databases in production? I know microsoft provide some scripts/stored procedures to handle it. I like to know is that recommend to use them in productions?

    Wednesday, August 29, 2018 5:04 PM

All replies

  • Are all the SQL Agent Jobs configured and running without error?
    Wednesday, August 29, 2018 5:07 PM
  • yes all are running and show successful in history, what is recommendation in this case?
    Wednesday, August 29, 2018 5:49 PM
  • The thing is, those are particularly large databases. 5GB isn't worth a second look in most cases.

    Has anyone adjusted the threshold down?

    Also, you should run BizTalk Health Monitor and resolve any Warnings it returns as well.

    Wednesday, August 29, 2018 5:57 PM
  • Yes I am Biztalk Health Monitor , no we did not adjusted threshold yet 
    Wednesday, August 29, 2018 6:01 PM
  • I do not see major warning, so what is recommendation shall we adjust threshold or shrink DB?  
    Wednesday, August 29, 2018 6:20 PM
  • I used Helth monitoring and find that spool size is big 148866.

    What is best way to reduce this in production. 

    Wednesday, August 29, 2018 9:48 PM
  • Are there any suspended messages in the message box database? If so is it okay if you clear them?

    Ideally the Backup BizTalk server job should clear out messages and logs. Are you sure that there are no errors and warnings in the history of the Back Up BizTalk server job? Is it modified by some one meaning any steps or such are deleted from the job?

    Some things to check

    1) Is the BackUp BizTalk server job modified any how and is DTAPurge And Archive job running as per schedule?

    2) Are there any suspended service instances

    3) Is there a dedicated Tracking Host Instance which is just used for Tracking? 

    4) Do you have proper SSIS packages which purge the data from the BAM Primary Import? These jobs will clear the data older than the threshold and will reduce the size of the BAM Primary Import database.

    Note: Yes there are scripts to hard delete and purge the data, bu I would advise you not to do it on the production environment. Please try to analyze the environment from the perspective of above questions and then let us know.

    Mandar Dharmadhikari

    Wednesday, August 29, 2018 11:41 PM
  • 148866 is a very large number of messages in the spool table

    Do you have many suspended message or many messages without refcount?

    One of the newer biztalk jobs is called Monitor BizTalk Server (BizTalkMgmtDb)

    Do you have this job configured and running without errors?

    Also report here all errors from Health Monitor, run using default profile
    Please check Key Indicators and MessageBox Database Integrity and report it here

    Do you know if checks in health monitor have been edited/changed?


    When asking a question please be as thoroughly as possible this will make it easier to assist you

    Thursday, August 30, 2018 8:02 AM
  • Is at least one Tracking Host running?

    There are no error in the Event Log?

    Thursday, August 30, 2018 11:36 AM
  • No error just warning in event log for one host instance 
    Thursday, August 30, 2018 5:56 PM
  • Yes I check history Backup BiztalkServer job and it failed couple of time in past . But yesterday it fixed by DBA . But past it failed couple  of times. 

    Is that  could be reason for spool size?

    What is option now other than fixing job ?

    Thursday, August 30, 2018 5:59 PM
  • Thanks

    I check this job and I observed this job failed  couple of time in past and ran successfully yesterday only and so I could not it in Health monitoring yesterday. But History I can see failure . I guess this stack this numbers .

    At this time that error resolved , any other recommendation to clean spool gracefully ?

    Thursday, August 30, 2018 6:13 PM
  • Sorry but what exactly did the DBA do to "fix" the job?

    You should not change/edit the job to prevent it from failing, but rather use BHM to clean up data and prevent the job from failing

    Has the DBA "fixed" other issues along the way?
    Your DBA should really not mess around and "fix" BizTalk related stuff in this manner


    When asking a question please be as thoroughly as possible this will make it easier to assist you

    Thursday, August 30, 2018 6:17 PM
  • I can understand your view , but organization policies I can not do much . But will provide details by asking DBA

    Thursday, August 30, 2018 6:43 PM
  • HOLD ON!  You need to find out exactly what the DBA did.  

    Remember, DBA's should never, ever, under any circumstances touch app code, which is what the BizTalk jobs are.  They always break things.  (Ok, maybe a slight exaggeration, they only break things 99 of 100 times ;)

    But seriously, there should be no need for them to do anything and if it's the case where a 'DBA' is the only one allowed to touch a SQL Server, you need to watch everything they do.

    However, the Backup job would not affect the Spool size.

    There's no Suspended or Pending Instances or anything? 

    Thursday, August 30, 2018 7:59 PM
  • Ok Thanks

    Right now no suspended or pending message. But as an average we used to have 5-10 due to edi validation errors. But we read error log in table and terminate them periodically.

    Thursday, August 30, 2018 9:04 PM
  • is there a dedicated tracking host that is working correctly?

    Are all BizTalk jobs running?

    Is the SSIS package for the Activities deployed to BAM Primary Import database working and cleaning out data?

    Mandar Dharmadhikari

    Friday, August 31, 2018 12:13 AM
  • No, we do not have dedicated tracking host,we disabled tracking in prod

    yes all jobs running

    regarding SSIS package I will double check

    Friday, August 31, 2018 5:51 PM
  • You sill need a Tracking Host running.  It's ok to not have a dedicated one, but you still need one running.

    Now, why did you disable tracking in prod?  Exactly what performance characteristic was specifically measured to justify this?  Otherwise, re-enable it.  Tracking uses very little overhead and after, say, 2006, disabling it always resulted in more problems that in solved (which is 0) because it took away a valuable diagnostic tool.

    Friday, August 31, 2018 6:17 PM
  • Spool size could be due to a few things. I would check to begin with -

    1) Are there too many suspended instances/In Progress instances?

    2) In SQL Agent, check the Job History of 'MessageBox_Message_Cleanup'. Is it getting invoked successfully?

    Thanks Arindam

    Thursday, September 6, 2018 6:50 AM
  • not suspended message, but as mentioned we used to get 5 to 10 every day due to data issue.

    Regarding In Progress, we have around 38 Running service Instance every time ( they are related to EDIBatch)

    Thursday, September 6, 2018 6:00 PM
  • I'd reckon it's due to the large numbers of EDI Batches then. If you are holding on to the batch for a while, this may happen. In this case, one option would be to increase the default 'Message count in DB' size to a higher value (since this is an exceptional case).

    Thanks Arindam

    Thursday, September 6, 2018 11:57 PM