Sql-server – Unable to shrink log file due to transactional replication

replicationsql servertransactional-replication

I have a transactional replication configured as per below:

ServerA.DBA (pub) --> ServerB.DBB (Sub) 
ServerB.DBB (pub1) --> ServerC.DBC (Sub)  
ServerB.DBB (pub2) --> ServerD.DBD (Sub)
ServerB.DBB (pub3) --> ServerE.DBE (Sub)  
ServerB.DBB (pub4) --> ServerF.DBF (Sub) 
ServerB.DBB (pub5) --> ServerG.DBG (Sub)

Today, I got an alert saying that my replication ServerA.DBA (pub) –> ServerB.DBB (Sub) is failing with below error:

Replication-Replication Distribution Subsystem: agent failed. The
transaction log for database is full due to 'REPLICATION'.

Upon investigation, I found that my log file was capped at 100GB on ServerB.DBB which was causing the issue. To fix this, I added a new log file as I was unable to extend the existing log file due to same error. This fixed my issue and replication caught up in some time. However, now I am unable to shrink the log file and it always shows my log file utilization as 99% and increasing. This raised some doubts in my mind whether my replication from Server B to C,D,E,F and G is actually running or not and why I am unable to shrink the log file.

Log_reuse_wait_desc on my ServerB.DBB always shows 'REPLICATION'. I ran DBCC OPENTRAN on my ServerB.DBB and it shows below:

Replicated Transaction Information:
Oldest distributed LSN     : (0:0:0)
Oldest non-distributed LSN : (10417370:9406:1)

This value is not changing since many hours now. Does it mean my replication from Server B to C,D,E,F and G is not working? However when I see in replication monitor it shows everything as fine. i.e. logreader, all distributor agents are running and moving transactions for all publications. Why I am unable to shrink the log file and what should I do to shrink the log file?

Also, want to add that CDC is not enabled on any of my DBs involved in replication. DBCC LOGINFO shows all my VLFs in use.

Best Answer

In case the solution i gave in comment doesn't works try this :

As you said : Log_reuse_wait_desc on ServerB.DBB always shows 'REPLICATION'

This means Replication is causing the log not shrinking problem.

DBCC OPENTRAN gives you the traction that is blocking as output which in your case is following :

Replicated Transaction Information:
Oldest distributed LSN     : (0:0:0)
Oldest non-distributed LSN : (10417370:9406:1)

To see which database still needs to be replicated use :

DBCC loginfo

If you are sure everything is replicated to all subscribers run the following query on the each database where the replication is completed.I will repeat make sure everything is replicated before executing this query as it marks all replications as successful and your replication queue will be empty.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Back up the log file and then try shrinking it now it should free up the space.

Source: https://stackoverflow.com/questions/1238491/how-to-shrink-transaction-log-in-sql-server-database-in-replication

Related Solutions

Sql-server – SQL Server 2008 transaction log growing since SP3 Installed

I'd suggest the 2 are unrelated.

Has anything changed:

in the maintenance plan setting
recovery model of the database (is it now FULL where it was SIMPLE)
did you take log backups before (FULL) in the maintenance plan? Do you still do them?
did you have auto shrink in the maintenance plan? This is bad anyway
have you any open transaction that is preventing a log backup (or shrink)

Also, restore a database from pre-SP3 on another server and compare the sys.databases entries

Sql-server – How to do a one-time log shrink on a database with transactional replication

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be:

ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([logfilename], 1)
ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT

Changing the database to simple recovery may cause transactions to stop being replicated, but if scheduled during off hours it should shrink the log and return to full recovery with no side effects.

If that is unacceptable, you can use the sp_repldone stored procedure to manually mark transactions as distributed and reset the replication status of invalid transactions:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

Best Answer

Related Solutions

Sql-server – SQL Server 2008 transaction log growing since SP3 Installed

Sql-server – How to do a one-time log shrink on a database with transactional replication

Related Question