LOG_REUSE_WAIT_DESC showing as 'REPLICATION' and also SNAPSHOT Publication Configured.

I would like to rephrase the sentence like this as "Size of the Transaction log of a database which is in Snapshot publication has been increasing and not able to shrink or truncate because log_reuse_wait_desc showing as REPLICATION"

To work on this issue i google like anything every where i found log_reuse_wait_desc showing 'REPLICATION' but replication is not configured. After i put a certain amount of time on google i found a MSDN article ,only one article which described about this. And i want to document that in my own words in my blog. One day in my service now dash board i got a ticket saying as Transaction log file of a particular database has been increased. As a part of troubleshooting i check teh below things. Assume here my database name is TEST

Findings:

1)  Test Database is in simple recovery model

2)  If i run the below command i found log_reuse_wait_desc showing as 'REPLICATION'

3) select log_reuse_wait,log_reuse_wait_desc,* from SYS.DATABASES

4) And i have Snapshot publication configured on this database and it is running fine

5) First i would like to check is there any open transactions are causing to this issue, as part of it i ran

DBCC OPENTRAN command on this database then i found values like this.

Transaction information for database 'test’.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (43831:51:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

6) The main thing that we need to focus here is Oldest non-distribute LSN value. So this oldest undistributed command causing to the log growth of that particular database.

7) And this is a known issue in Snapshot publication which causes DDL changes on the table which have schema changes marked for replication and this is causing to the issue.

8)  So as a workaround we need to run the below command to clear the oldest non-distributed LSN

EXEC sp_changepublication

@Publication='PublicationNameHere',

@Property=N'replicate_ddl',

@value=0

9) Once you run the above command you will get a message saying as "Publication is updated successfully"

10) Next again you run the DBCC OPENTRAN(test) command and see that there is no Oldest distributed transaction now.

11) Since the database recovery model is in 'SIMPLE' run CHECKPOINT command once or twise

12) Next check the "log_reuse_wait_desc" value under sys.databases table. You can find 'ACTIVE TRANSACTION' or 'NOTHING' from the column

13) Now this time run the shrink command like mentioned below

USE test

GO

DBCC SHRINKFILE(N'Test_Log',2048)

15) I am shrinking the log file to 2 GB. And it will shrink now.

The above information extracted from the below MSDN link.

https://blogs.msdn.microsoft.com/sqlserverfaq/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication/

Sometimes i got the same issue with in Transactional Publication, Here database is also in SIMPLE recovery model but publication is Transactional
now this time i use below command to flush out Oldest distribution transaction

USE PublisherDatabaseName
GO
EXEC sp_repldone null, null, 0,0,1

The above command did the trick.