Recently, a friend of mine who is working with me on a project sent 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)
Immediately, I pointed out to the team that this issue was related to a lack of disk space.
📝 One-Minute Brief
Explains how to diagnose and fix the BizTalk Server error where the BizTalkMsgBoxDb transaction log becomes full due to LOG_BACKUP, including the root cause and the steps required to restore normal processing.
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 of 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 which we were doing backup ran out of disk space, because we cannot do the backups until the transaction log grows until the point that the disk (that contain the log file) also runs 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 has grown quite large, you should consider stopping your BizTalk Server environment and performing database maintenance, especially to reduce the transaction log size.
For that, you should:
- Perform a full backup 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.
Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son.
