Restored DB to new server server has DB stuck with log_reuse_wait_desc = REPLICATION

Question

Restored DB to new server target server has DB stuck with log_reuse_wait_desc = REPLICATION, Simple recovery model, cannot be shrunk or freed up.

Is there no easy way to tale a restored database out of log_reuse_wait_desc = REPLICATION?

The source of this DB restore was a replicated system. The target of this DB restore is not a replicated DBMS yet but that option is installed for future use.

There are no publications on this new server - Distribution DB is not setup yet. So disabling Publishing and Distribution fails, because it does not exists.

select * from sys.databases where is_published=1 = no rows

log_reuse_wait_desc = REPLICATION for this DB even though there is no replication here, but it was restored from a replication system.

Checkpoints do not help either. All steps to remove replication do not help - because replication does not really exist on the new target server - no publisher, no distributor, no jobs, no subscriber or subscription and all attempts to remove none existent replication objects fail.

Even blogs that talk about DBCC open transactions and resetting as a work around fail

Msg 14013, Level 16, State 1, Procedure sys.sp_MSrepl_changepublication, Line 114 [Batch Start Line 45]

This database is not enabled for publication.

Is my only option to try to setup and then delete a bogus replication? This is the only this I read that I have not tried yet. There must be a better way!

Please let me know if anyone has experience with this issue. Thanks.

Answer 1

Are you able to put the DB into full recovery model, set the server up for replication (you will need to add it from setup if it's not already present), create a publication in the problem DB, publish a table (any table) and then completely remove replication?

It's obviously stuck in a situation where it's a copy of a database that didn't have replication removed.

You might not even need to publish a table. Can you just try:

-- Enable distribution (which will need a distribution database) then do the following:

-- Enable replication for the DB
EXEC sp_replicationdboption @dbname = 'TheDatabase', @optname = N'publish', @value = N'true';
GO

-- Remove the publication
-- This command needs the name of the publication (Refresh in SSMS to check the name)
EXEC sp_removedbreplication 'SomePublication_TheDatabase','both';
GO

-- Disable replication for the DB
EXEC sp_replicationdboption @dbname = 'TheDatabase', @optname = N'publish', @value = N'false';
GO

-- Then remove replication/distribution