Performance issue : a love story with the SQL Agent

Hey everybody,

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

USE master
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
GO
Use BizTalkMgmtDb
DBCC SHRINKFILE (N’BizTalkMgmtDb_log’ , 0, TRUNCATEONLY)
GO

Use BizTalkDTADb
DBCC SHRINKFILE (N’BizTalkDTADb_log’ , 0, TRUNCATEONLY)
GO
Use BizTalkMsgBoxDb
DBCC SHRINKFILE (N’BizTalkMsgBoxDb_log’ , 0, TRUNCATEONLY)
GO

USE master
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
GO

Publicités

Laisser un commentaire

Choisissez une méthode de connexion pour poster votre commentaire:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s