The transaction log for database BizTalkMsgBoxDb is full - Steps to fix this error
Jan 10, 2014
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.
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.
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;
DBCC SHRINKFILE (BiztalkMsgBoxDb_log, 1);
ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
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 */
/* Method Two */
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.
Detailed TSQL Used by each step of Backup BizTalk Server (BizTalkMgmtDb) Job
/* Step One - BackupFull */
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'DRIVE:\BACKUPLOCATIONPATH\DATABASE_FULL\BTS' /* location of backup files */
/* Step Two - MarkAndBackupLog */
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'DRIVE:\BACKUPLOCATIONPATH\\DATABASE_TLOG\BTS' /* location of backup files */
/* Step Three - Clear Backup History */
exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14
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.
Geeks who read this article also read…
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- Time required to complete database backup and restore in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…