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.

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'
  • Replaced the @optname with publish and ran it. Got the following message but Wait_Desc is still replication. "The replication option 'publish' of database 'DBName' has been set to false." – MrTCS May 14 '14 at 15:20
  • name is_published is_subscribed is_merge_published is_distributor DBName 0 0 0 0 – MrTCS May 14 '14 at 15:31
  • Tried that along with sp_replflush and still no luck. – MrTCS May 14 '14 at 15:36
  • Do you have CDC enabled on the database? – David Browne - Microsoft Jul 13 at 16:02

I would first try running below command to see if that fixes the issue.

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

If not, I will create a publication for a single article and then run the above command again. This should mark all the replication transactions as complete. Once I am able to shrink the log file, I'll remove my publication.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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