On one of the BizTalk servers were many of BizTalk WCF service are published started giving Timeout issue (it was working absolutely fine before that).
The first thought after seeing timeout error is to check the WCF timeout settings, but it couldn't be the reason as those were already Configured while deployment. So it was something else which was to look upon.
Upon visiting the event viewer, found a warning - BizTalkIsolatedHost throttled because DatabaseSize exceeded the configured throttling limit
Learning - Never ignore Warnings ;)
So it was MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb which was failing continuously with below error.
Message
Executed as user: xxx\xxxx. The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed
MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb calls the MessageBox_Message_Cleanup_BizTalkMsgBoxDb which deletes the message which have fulfilled the subscription . Whenever message is published in Message box, subscribers get copy of the message and not the actual message as messages in BizTalk are immutable. Upon fulfilling the subscriptions messages are marked for deletion.
Thus messages were getting piled up.
The first thought after seeing timeout error is to check the WCF timeout settings, but it couldn't be the reason as those were already Configured while deployment. So it was something else which was to look upon.
Upon visiting the event viewer, found a warning - BizTalkIsolatedHost throttled because DatabaseSize exceeded the configured throttling limit
Learning - Never ignore Warnings ;)
Why it happened
The most probable reason for increase in database size is due to misbehavior of the jobs which are intended to keep everything flowing smoothly in SQL(BizTalk). Thus it needs to be validated that the jobs are configured properly and also if required clean/shrink the database.So it was MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb which was failing continuously with below error.
Message
Executed as user: xxx\xxxx. The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed
MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb calls the MessageBox_Message_Cleanup_BizTalkMsgBoxDb which deletes the message which have fulfilled the subscription . Whenever message is published in Message box, subscribers get copy of the message and not the actual message as messages in BizTalk are immutable. Upon fulfilling the subscriptions messages are marked for deletion.
Thus messages were getting piled up.
