Dear BizTalk experts,

I need you support and recommendation regarding issue:

In our production environment we are processing 900+ messages per minute and this grows the size of "BizTalkMsgBoxDb" and when "BizTalkMsgBoxDb" or "BizTalkMsgBoxDb_log.ldf" exceeds its size more than 4 GB BizTalk got very slow in processing other messages.

each time when "BizTalkMsgBoxDb" exceeds 4GB I just un-configure the BizTalk and delete databases "BizTalkMsgBoxDb" and "BizTalkDTADb" and then reconfigure the BizTalk again, doing so BizTalk processes 900+ messages per minute.

I know this is worst practice what I am doing. Now we are expecting more than 2000 messages per minute and I want to fix this issue without un-configuring the BizTalk.

Kindly suggest me best practices when the size of "BizTalkDTADb" and "BizTalkMsgBoxDb" exceeds more than 4GB, even I have much disc available.

I do not want to keep the old messages information in "BizTalkMsgBoxDb" or in "BizTalkDTADb".

If someone suggests or provide the script to clean "BizTalkMsgBoxDb" or in "BizTalkDTADb" it will be highly appreciated.

To clear the "BizTalkMsgBoxDb_log.ldf" I am using below scripts:

USE master

ALTER DATABASE BizTalkMgmtDb SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE BizTalkDTADb SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE BizTalkMsgBoxDb SET RECOVERY SIMPLE WITH NO_WAIT

GO

Use BizTalkMgmtDb

DBCC SHRINKFILE (N'BizTalkMgmtDb_log' , 0, TRUNCATEONLY)

GO

Use BizTalkDTADb

DBCC SHRINKFILE (N'BizTalkDTADb_log' , 0, TRUNCATEONLY)

GO

Use BizTalkMsgBoxDb

DBCC SHRINKFILE (N'BizTalkMsgBoxDb_log' , 0, TRUNCATEONLY)

GO

USE master

ALTER DATABASE BizTalkMgmtDb SET RECOVERY FULL WITH NO_WAIT

ALTER DATABASE BizTalkDTADb SET RECOVERY FULL WITH NO_WAIT

ALTER DATABASE BizTalkMsgBoxDb SET RECOVERY FULL WITH NO_WAIT

GO

900 messages/minute is ok and even 2000+ messages is ok but it depends on what your deployment infrastructure looks like... for e.g.: how many servers? are BizTalk servers segregated from your DB servers.. on the db server are you using local disks or SAN disks... are all your databases on the same disk/LUN etc. etc.

While there are some recommended sizes for MessageBox DB and 4 GB is definitely within the limit but there is still very little information to give you recommendations/analyze the root cause for this behavior.

Regards.

Pi_xel_xar,No SQL Agents is not enabled even.

Actually I am new in BizTalk development, now what should I have to do ?

Please advise

-Masood

Shankycheil,

There is only one server on which BizTalk is running, the database on which BizTalk inserts the data taken from web service is on another server.

-Muhammad Masood

Hi,

The jobs should be running for a healthy environment. It takes care of clean up etc.

Please go thru 

https://support.microsoft.com/en-us/kb/919776

http://social.technet.microsoft.com/wiki/contents/articles/7248.biztalk-server-2010-sql-agent-jobs.aspx

Hi Muhammad,

You need to optimize your Production environment for Max throughput . It is step by step process to validate your Integration Environment so that it can give you max performance .

Following are the consideration while designing your Integration Infrastructure.

1) Benchmark your Biz Talk Environment as per the load which your business is expecting .

2) If you are working on single stand alone  server than the choice would be to cluster SQL instance in Active/Passive or Active/Active state.

3) Keep the biztalk  SQL agent jobs up and  running . It is essential for better health of BizTalk DB'S.

4) Set auto growth of SQL databases refer https://sandroaspbiztalkblog.wordpress.com/2012/09/18/pre-allocate-space-and-define-auto-growth-settings-for-biztalk-server-databases/

5) Run MessageBox viewer tool to validate any inconsistency or lock in BIzTalk Databases.

Thanks

Abhishek