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, also it worked fine in load scenario too. 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, also it worked fine in load scenario too. 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.
What to do
Somehow there was access issue for the account, which was granted and the job MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb was enabled. By default MessageBox_Message_Cleanup_BizTalkMsgBoxDb is disabled and should not be run manually.If you have questions or suggestions, feel free to do in comments section below !!!
Do share if you find this helpful .......
Knowledge Sharing is Caring !!!!!!
What was the database size of your host set to?
ReplyDeleteThanks It was really helpful..
ReplyDeleteWhat is the Ideal credentials we should use for SQL Job Configuration.
ReplyDelete