The transaction log for database BizTalkMsgBoxDb is full - Steps to fix this error


Jan 10, 2014

Introduction

In this article we will take a look at the steps which you need to follow when you receive “The transaction log for database ‘BizTalkMsgBoxDb’ is full” error.

Error Message:

The transaction log for database ‘BizTalkMsgBoxDb’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (.Net SqlClient Data Provider)

First thing would be to confirm the log space used by the BizTalkMsgBoxDb database. You can find this information by executing the below script.

DBCC SQLPERF(LOGSPACE)
GO

DBCC SQLPERF LOGSPACE Output

In the above snippet you could see that the log file of BizTalkMsgBoxDb database is 100% full.

How to fix Transaction Log for database BizTalkMsgBoxDB is full error message

To resolve this issue execute the below mentioned script which changes BizTalkMsgBoxDb database to Simple Recovery Model, Shrinks the Transactional Log file to 1 MB in Size and then Set the Recovery Model back to Full. Learn More about Different Recovery Models in SQL Server.

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY SIMPLE;
GO

Use BiztalkMsgBoxDb
GO
DBCC SHRINKFILE (BiztalkMsgBoxDb_log, 1);
GO

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO

How to Verify Current Size and Space Used by the BizTalkMsgBoxDb Database Transaction Log File

To verify the current size and space used by the transactional log file of BizTalkMsgBoxDb database. One can either run SP_HELPDB command or DBCC SQLPERF(LOGSPACE).

/* Method One */

Use Master
GO
SP_HELPDB BiztalkMsgBoxDb
GO

/* Method Two */

DBCC SQLPERF(LOGSPACE)
GO

How to Perform an Ad-Hoc Full Backup of BizTalk Databases

In case if you wish to perform an Ad-Hoc Full Backup of all the BizTalk Databases then you must run dbo.sp_ForceFullBackup system stored procedure which is found in BizTalkMgmtDB Database. Once this stored procedure is executed the next run of “Backup BizTalk Server (BizTalkMgmtDb)” will ensure a full backup of data and log files. By default “Backup BizTalk Server (BizTalkMgmtDb)” job is scheduled to run once in every 15 minutes.

Overview of Backup BizTalk Server (BizTalkMgmtDb) SQL Server Agent Job

The Backup BizTalk Server (BizTalkMgmtDb) Job performs full database backups (step 1) and log backups (step 2) of all BizTalk Server databases.

Backup BizTalk Server (BizTalkMgmtDb) Job in SQL Server

Detailed TSQL Used by each step of Backup BizTalk Server (BizTalkMgmtDb) Job

/* Step One - BackupFull */

Use BizTalkMgmtDB
GO
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'DRIVE:\BACKUPLOCATIONPATH\DATABASE_FULL\BTS' /* location of backup files */
GO

/* Step Two - MarkAndBackupLog */

Use BizTalkMgmtDB
GO
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'DRIVE:\BACKUPLOCATIONPATH\\DATABASE_TLOG\BTS' /* location of backup files */
GO

/* Step Three - Clear Backup History */

Use BizTalkMgmtDB
GO
exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14
GO

 

Conclusion

As a best practice one must regularly monitor the success of Backup BizTalk Server (BizTalkMgmtDb) SQL Server Agene Job to avoid similar issues in future.

Share this Article


Geeks who read this article also read…




"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."






Follow @MyTechMantra on Twitter