Sql-server – After Restore Log_reuse_wait_desc of Replication

replicationrestoresql serversql-server-2012

I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it. We are restoring a database into lower Dev environments and after the restore the log_reuse_wait_desc is Replication. The problem is we are not using keep replication or CDCs.

This happens in a couple of different setups but I'll keep this initial one simple.

Database from a 2008 instance restored to a 2012 instance. Only way to clear it out is to create a test publication on any random table, no subscription needed, and log_reuse_wait clears.

Best Answer

Can you try this following script on your database? 

use master
exec sp_replicationdboption @dbname = N'your db name', @optname = N'<<name of your 
publication>>', @value = N'false'
GO

select name, is_published, is_subscribed, is_merge_published, is_distributor
from sys.databases where name =<<your database name>>

This might be due to orphaned replication metadata. You can use the below query to remove:

USE master
EXEC sp_removedbreplication
    @dbname = 'myDB'
Related Question