0

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.