Hi,

We have been recently been doing some performance testing on our system, and when running one of our service requests approximately 40 000 times the log file has been growing extremely large. The backup last night ended up at 160 GB. We have been running various cleanup scripts but this hasn't helped with the backup size. 

The service has approximately 26 message box hits. I've been developing in BizTalk for some time but have little experience with the database side of things. I'm unsure if there is any specific logging that I should be disabling but as this is related to the SQL log, I'm not sure if there are any BizTalk settings I can change.

I have disable all tracking except for the Group Tracking setting on the host configuration. 

Any thoughts would be appreciated.

Hi Nolan,

This is not normal.

Seems your BizTalk database Jobs are not running , Make sure your BizTalk SQL servers jobs have been enabled and SQL server agent is running. 

Please have a look of How to Configure the Backup BizTalk Server Job article to enable the jobs. 

The BizTalk backup job is responsible for keeping the log file size in the limit. 

you can try shrinking the log file. Please have a look on related thread

MessageBox log file size

Please follow below useful article for reference,

Performance issues in BizTalk and How to maintain and troubleshoot BizTalk Server databases

BizTalk Databases: Survival Guide

Very important question: Are you asking about the Management database of the MessageBox database?

Also, you mean 26 MessageBox hits per invocation?

Hi,

There are quite a few jobs that runs in BizTalk DB to keep it clean and als takes regular backup of the DB. You need to check which DB (MsgBox or ManagmentDB) is getting large.

The link below gives the details of the Jobs that you need to check if it is running fine on your environment:

SQL Server Agent jobs in BizTalk Server

Specially look for the following jobs in your BizTalk DB environment:

• MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb
• Backup BizTalk Server

Rahul

Hi,

If you are referencing MessageBox hits, there are transaction records which could be the major cause for high memory consumption. As mentioned by experts you will have to be careful with the purges and have scheduled cleanups in production, your admin has do that anyways. 

If you are referring to development, take a simple route of shrinking the database. ONLY DEVELOPMENT NOT PRODUCTION

use [DatabaseName]
go
BACKUP LOG [DatabaseName] TO DISK='NUL'
dbcc shrinkfile([DatabaseName_LogfileName]_log, truncateonly)
go

Also take a backup before you perform the shrink. 

K

Very important question: Are you asking about the Management database of the MessageBox database?

Also, you mean 26 MessageBox hits per invocation?

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

Very important question: Are you asking about the Management database of the MessageBox database?

Also, you mean 26 MessageBox hits per invocation?

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

Step-by-step:

1. Verify there is at least one running instance of a Tracking Host.

2. Verify all the SQL Jobs are configured (DTA Purge And Archive and Backup BizTalk Server require configuration) and runnign without error.

Also run BizTalk MessageBox Viewer to capture errors and it would suggest you the possible causes and its solutions. If MessageBox Viewer identifies this issue is due to Messages without reference counts, it would suggest you to run the terminator tool.

Also if all jobs are running as intended without any warnings/errors in the SQL Server event log, your backup job is configured, and you don't have many running/suspended instances, then I would assume that the files has grown because there used to be a problem that is now resolved.

In that case I would stop my BizTalk host instance(s) and shrink both the MessageBox data file and log file, by running the following:

Use
BizTalkMsgBoxDb
DBCC SHRINKFILE (N'BizTalkMsgBoxDb' , 0, TRUNCATEONLY)
GO


DBCC SHRINKFILE (N'BizTalkMsgBoxDb_log' , 0, TRUNCATEONLY)
GO

Very important question: Are you asking about the Management database of the MessageBox database?

Also, you mean 26 MessageBox hits per invocation?

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

Nolan,

I have already faced the same issue. I would suggest to please look into below article, that will help to resolve your issue.

Issue: Size of BizTalk tracking databases grows rapidly and causes poor disk performance or fills up the disk subsystem.

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

If the log file continues to grow, then the Backup BizTalk Server Agent Job is not configured or running.

1. Configure the Backup BizTalk Server job.
2. Ensure the job runs successfully.

Instructions: https://msdn.microsoft.com/en-us/library/aa546765.aspx

After at least one successful backup, you will have to manually shrink the files.

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

If the log file continues to grow, then the Backup BizTalk Server Agent Job is not configured or running.

1. Configure the Backup BizTalk Server job.
2. Ensure the job runs successfully.

Instructions: https://msdn.microsoft.com/en-us/library/aa546765.aspx

After at least one successful backup, you will have to manually shrink the files.

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator 3 hours 56 minutes ago

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

If the log file continues to grow, then the Backup BizTalk Server Agent Job is not configured or running.

1. Configure the Backup BizTalk Server job.
2. Ensure the job runs successfully.

Instructions: https://msdn.microsoft.com/en-us/library/aa546765.aspx

After at least one successful backup, you will have to manually shrink the files.

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator Monday, September 07, 2015 3:07 AM

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

If the log file continues to grow, then the Backup BizTalk Server Agent Job is not configured or running.

1. Configure the Backup BizTalk Server job.
2. Ensure the job runs successfully.

Instructions: https://msdn.microsoft.com/en-us/library/aa546765.aspx

After at least one successful backup, you will have to manually shrink the files.

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator Monday, September 07, 2015 3:07 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator 18 hours 35 minutes ago

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

If the log file continues to grow, then the Backup BizTalk Server Agent Job is not configured or running.

1. Configure the Backup BizTalk Server job.
2. Ensure the job runs successfully.

Instructions: https://msdn.microsoft.com/en-us/library/aa546765.aspx

After at least one successful backup, you will have to manually shrink the files.

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator Monday, September 07, 2015 3:07 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator Tuesday, September 08, 2015 12:26 PM

Hi, to clarify, I'm referring to the log file of the BizTalkMsgBoxDb. 

Yes, approximately 26 message box invocations per request.

If the log file continues to grow, then the Backup BizTalk Server Agent Job is not configured or running.

1. Configure the Backup BizTalk Server job.
2. Ensure the job runs successfully.

Instructions: https://msdn.microsoft.com/en-us/library/aa546765.aspx

After at least one successful backup, you will have to manually shrink the files.

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator Monday, September 07, 2015 3:07 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator Tuesday, September 08, 2015 12:26 PM