Solve The transaction log for database Database is full and solve log_space_reuse_wait as “Replication"

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

SQL SERVER – Huge Transaction Log in Snapshot Replication!

In a situation where the log space for the database is not getting reused and it is increasing in size. As usual, I asked to check sys.databases catalog view and we found that the log_space_reuse_wait as “Replication”.

The client has confirmed that they have replication configured on the database and it was SNAPSHOT replication.

It was not making sense to me why snapshot replication would keep a hold on LDF file. I asked to run DBCC OPENTRAN command and found below

Replicated Transaction Information:

Oldest distributed LSN     : (0:0:0)

Oldest non-distributed LSN : (82:2496:1)

Again, this was not making sense as snapshot doesn’t use transaction log to generate snapshot files. I went a step further and did below mathematics.

82 decimal = 52 hex
2496 decimal = 9C0 hex
1 decimal = 1 hex

So LSN in the output is 00000052:000009c0:0001

Select [Transaction ID] from fn_dblog(NULL, NULL)
where [Current LSN] = '00000052:000009c0:0001'

Found 0000:0000052c so we looked into log again to see the transaction information.

Select * from fn_dblog(NULL, NULL)
where [Transaction ID] = '0000:0000052c'

It was some ALTER TABLE command. Found below MSDN article

How to: Replicate Schema Changes

We executed below to find details about publication. The name is “snap”

sp_helppublication 'snap'

To fix the issue, I ran below

exec sp_repldone null, null, 0,0,1

NOTE: Above command is safe in snapshot replication because it marks _all_ transactions as distributed. This would cause problems in another type of replications.

Once above command is run, we need to run the snapshot agent again and then go to a regular procedure to shrink the T-Log file. Now, to prevent such incident in the future, I have asked to execute the following T-SQL batch to disable schema changes replication:

-- Sample Query
DECLARE @publication AS sysname
SET @publication = N'snap'

-- Turn off DDL replication for the publication.
USE Test
EXEC sp_changepublication
@publication = @publication,
@property = N'replicate_ddl',
@value = 0
GO

This was a learning for me that ALTER can cause issue with snapshot replication.