BizTalk slow? The database might need some cleaning up…

We’ve hade some problems earlier with a very slow BizTalk installation. It could takes minutes (!) to consume messages from MSMQ queues and the message queues can build up pretty quickly if throughput is that bad.

It turned out that the main problem was that the messagebox database was full of stale messages that should have been cleaned up, but wasn’t since the database job MessageBox_Message_Cleanup_BizTalkMsgBoxDb was not scheduled (which it should be).

To see the difference this makes, we can first analyze the size of the databases:

USE [BizTalkMsgBoxDb]
GO
SELECT charindex('log',name), name ,size/128.0 AS Size, CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS SpaceUsed, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO
    name                 Size             SpaceUsed      AvailableSpaceInMB
0   BizTalkMsgBoxDb        4840.000000    4652.812500     187.187500
17  BizTalkMsgBoxDb_log    4000.000000    38.453125      3961.546875

In this case we have a really big BizTalkMsgBoxDb database, Running the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job makes things a lot better:

    name                 Size             SpaceUsed      AvailableSpaceInMB
0   BizTalkMsgBoxDb        4840.000000     167.562500    4672.437500
17  BizTalkMsgBoxDb_log    4000.000000    3693.523437     306.476563

After this our BizTalk installation has been much quicker and now works as it should.

BTW, if the above does not solve the problem then it may help checking that another database job called “DTA Purge and Archive (BizTalkDTADb)” is also scheduled. If it isn’t then the tracking database will grow indefinitely which is not a good thing at all…

Note: Errors like these are easy to find using the BizTalk MessageBoxViewer tool that I can really recommend. Download it here: http://blogs.technet.com/b/jpierauc/archive/2007/12/18/msgboxviewer.aspx

Fixing CLR trigger error after installing .Net Framework updates

Today I discovered an error in one of our CLR triggers in a SQL Server database:

System.Data.SqlClient.SqlException: A .NET Framework error occurred during execution of user-defined routine or aggregate "PersonalChangedTrigger": 
System.IO.FileLoadException: Could not load file or assembly 'System.Messaging, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

This was kind of unexpected since we didn’t modify the trigger. It turns out that it was probably a Windows Update patch that modified the System.Messaging assembly so we had to “re-import” it into the database. This is how to do that, in our case:

ALTER ASSEMBLY Messaging
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE

/Emil