RAM Usage for SQL Server BizTalk Databases

Question

Hi,

We are moderate shop for BizTalk server, and have both BizTalk 2006 R2 and SQL Server 2005 on same server.
And sql server is dedicated to BizTalk databases only.
And we noticed SQL process has memory of close to 1.9 GB. And it is not releasing even when BizTalk is idle. And we noticed BizTalk is not picking up files from receive locations as it use to before. And some times we need to restart host instance after waiting for 20-30 min. This is causing so much delay in work flow.

Initially, I just want to restart the sql server service, that may release the memory.
Do we need to throttle the BizTalk server here ?  our processing has 50% orchestrations and 50% maps on the ports.


Any help on these issues is appreciated.

I know we need to have different servers for biztalk and sql server. And it is recommended to have two biztalk servers for high avaialability. We are going there in the next couple of months.

Thanks
Bhava

Answer 1

Hi,

One possible reason can be too many messages in the message box.  This normally happens when the SQL jobs (the job that copy messages from message box to dta and one that clears purge dta) are not functioning properly.  Please check the data and transaction log size of your messagebox and dta databases. Please run MessageBoxViewer http://blogs.technet.com/jpierauc/pages/msgboxviewer.aspx on your box.  It will indicate any issues with your settings and can help you in determining issues.

Regards,

Tariq Majeed

---

Please mark it as answer if it helps

Answer 2

Hi,

You can test the performance of your solution by adding performance counter via the Performance MMC.  See http://msdn.microsoft.com/en-us/library/aa578394(BTS.10).aspx

Answer 3

You need to look into the perfomance of the BizTalk Database jobs as they have run successfully. Sometimes when there is not enough memory for the back storage then also they fail so do look for that as well. Also terminate all the suspened and othere instances before restarting your host instances as othe way round it creates over head on BizTalk DB. Before running the back up jobs delete all the previous back up files or save them on some other place. To me it looks the storage problem. We referred below while paper in detail for the performace testing and optimization. You canb also refer that.

http://msdn.microsoft.com/en-us/library/ms864801(BTS.10).aspx

Some times sql resources also gets locked. Do run sp_who2 to find out which all processes are in use and then if not required you could kill them. Please let me know if you require some guidelines related to Performance testing and automation testing of BizTalk.

kill 'processsid'

Regards
Vishnu

---

Vishnu

Answer 4

You could also have a look at

http://msdn.microsoft.com/en-us/library/cc558617(BTS.10).aspx

Regards
Vishnu

---

Vishnu