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
DECLARE curTables CURSOR FOR
SELECT name from master..sysdatabases order by 1
–open the cursor
OPEN curTables
–loop through the cursor lines
FETCH NEXT FROM curTables INTO @vchrTable
WHILE @@FETCH_STATUS = 0
BEGIN
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
END
–clean up
CLOSE curTables
DEALLOCATE 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.
Technorati: 
BizTalk 2006 
MessageBox
