How do you manually change the 'log_reuse_wait' status in SQL Server?
On the Q &amp; A Transaction Log won&#39;t shrink, DB thinks it is replicating I found the answer by Matthias Elflein to be the most correct, though first I had to enable a distributor for it to work (I just enabled the default local distributor and deleted it afterwards).

Just in case that answer disappears for some reason, the code to remove a publication from restored database is:


USE DBname
GO
-- 1.) enable publication for MyDatabase
EXEC sp_replicationdboption 
  @dbname = 'DBname', 
  @optname = N'publish', 
  @value = N'true';
GO
-- 2.) remove publication from database. Use the PUBLICATION-name (not database name)
sp_removedbreplication 'Publ_DBname','both'

-- 3.) disable publication for MyDatabase
EXEC sp_replicationdboption 
  @dbname = 'DBname', 
  @optname = N'publish', 
  @value = N'false';
GO



I had to create a distribution database on server B before running the code above otherwise the query errored with the following message:

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 76
The Distributor has not been installed correctly. Could not enable database for publishing.
The replication option 'publish' of database 'DBname' has been set to false.
The replication option 'publish' of database 'DBname' has been set to false.




The above error only applied to SQL Server 2012. I found that when restoring these databases to SQL Server 2016 that there was no log wait status (since replication didn't seem to be installed on the machine).