Just for fun, some relations told me about their biztalks servers issues during load test sessions.
They rans too much slowly and get a lot of dead lock on the messagebox. I start by analyzing job agent state.
And here it is, the SQL agent is not RUNNING since a long time.
Consequence : the messagebox of course grows up. Immediatly, I enable the agent and start all essential job.
After 2 hours the jobs finished and the database size is nearly acceptable.
But the problem still exist, so I checked the log size and surprise they weight around 150go, the problem continues.
The job keeps the log history for 30days, so my client got two choices :
- wait one month in this mode
- purge the SQL log
My client chose the first as I advice him. the reasons why :
- This procedure is not supported by Microsoft
- The purge procedure is very dangerous
- As I said in the beginning, the issues are encountered only in load performance, for the existing flows there is no problems
Now log size is supervised they wait for the log diet
In conclusion, please check the job agent is very crucial.
For those who want what is the procedure to clear all BizTalk logs, here is the script
ALTER DATABASE BizTalkMgmtDb SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE BizTalkDTADb SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE BizTalkMsgBoxDb SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE (N’BizTalkMgmtDb_log’ , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N’BizTalkDTADb_log’ , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N’BizTalkMsgBoxDb_log’ , 0, TRUNCATEONLY)
ALTER DATABASE BizTalkMgmtDb SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE BizTalkDTADb SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE BizTalkMsgBoxDb SET RECOVERY FULL WITH NO_WAIT