BizTalk Server: The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’.

Recently a friend of mine that is working with me in a project send me an email reporting a quite curious issue that I found while accessing the BizTalk Server Administration console in our development environment:

This operation failed while accessing at least one of the Message Bix databases. Some results might be omitted. (Microsoft.Biztalk.Administration.SnapIn)

Additional information:

The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’. (Microsoft SQL Server, Error: 9002)

The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’

Immediately I point out to the team that this issue was related to lack of disk space.

Cause

The official cause of this issue is that when the transaction log becomes full, SQL Server Database Engine issues a 9002 error. The log can fill when the database is online, or in recovery. If the log fills while the database is online, the database remains online but can only be read, not updated.

And for us to know the exact cause for what is preventing log truncation, we can use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view.

In our case, it was indeed a problem with disk space and what happened was that the disk to where we were doing backup went out of disk space, because we cannot do the backups the transaction log grow until the point that disk (that contain the log file) also went out of disk space.

Solution

When you know the issue, the solution is quite easy. In this case, freeing disk space from both hard drives immediately solves the problem. Nevertheless, because the log file got quite big you should think of stopping your BizTalk Server environment and do maintenance in your databases in special reduce the size of the transaction log.

For that you should:

  • Perform a full back of your databases;
  • Stop all BizTalk Server services (host instances and enterprise Single Sign-on)
  • And run the following SQL Script
ALTER DATABASE BizTalkMsgBoxDb
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (BizTalkMsgBoxDb_log, 2048);
GO

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO
  • Do about the same steps for other databases whose transaction logs are also quite large.
Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published. Required fields are marked *

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top