BizTalk SIG Experience

Posted: June 21, 2007  |  Categories: BizTalk 2006 Uncategorized

Yesterday I led a Special Interest Group BizTalk Meeting. We discussed some customer scenarios, where we created BizTalk solutions for. It was a very successful exchange of knowledge and experiences. Solutions involved connections to legacy systems like AS/400, LOB like SAP and Oracle eBusiness suite, webservices (WSE) and SQL Server. Solutions were demoed to each other on VPC or through VPN to customer development environment. Experiences during development were discussed and practical matters were exchanged. One example of practical matter discussed was involved with jobs running under SQL Server agent to tidy up the MessageBox database for instance. If the SQL Server agent is not running (discovered later on when BizTalk application is running) one will experience performance degredation, because MessageBox is growing and growing (tables scans will be performed to match subscriptions on messages). To solve this there is a great post on internet, where two scripts are explained to clean up the MsgBox and DTADb database. I can tell you it works and BizTalk performance increased. To get rid of large database logs the following TSQL can be used:

–delcare variables
declare @vchrTable varchar(200),
@vchrMsg varchar(200)

–declare the cursor
SELECT name from master..sysdatabases order by 1

–open the cursor
OPEN curTables

–loop through the cursor lines
FETCH NEXT FROM curTables INTO @vchrTable
set @vchrMsg = ‘DBCC SHRINKDATABASE ( [‘ + @vchrTable + ‘] ,10);’
print @vchrMsg
exec (@vchrMsg)

set @vchrMsg = ‘backup log [‘ + @vchrTable + ‘] with truncate_only;’
print @vchrMsg
exec (@vchrMsg)

–backup log tstoday with truncate_only

FETCH NEXT FROM curTables INTO @vchrTable

–clean up
CLOSE curTables

This is just an example of one of the many experiences we exchanged and getting together and discuss BizTalk solutions together is great and meaningful. Links in this post will give you a better understanding of MessageBox performance and so on.


Author: Steef-Jan Wiggers

Steef-Jan Wiggers is all in on Microsoft Azure, Integration, and Data Science. He has over 15 years’ experience in a wide variety of scenarios such as custom .NET solution development, overseeing large enterprise integrations, building web services, managing projects, designing web services, experimenting with data, SQL Server database administration, and consulting. Steef-Jan loves challenges in the Microsoft playing field combining it with his domain knowledge in energy, utility, banking, insurance, healthcare, agriculture, (local) government, bio-sciences, retail, travel, and logistics. He is very active in the community as a blogger, TechNet Wiki author, book author, and global public speaker. For these efforts, Microsoft has recognized him a Microsoft MVP for the past 8 years.


Back to Top