Solve The transaction log for database Database is full

The transaction log for database BizTalkMsgBoxDb is full - Steps to fix this error
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

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