How do you manually change the 'log_reuse_wait' status in SQL Server?

Question

I'm in the process of moving several databases to a new server. All the databases have SIMPLE recovery mode, as they are backed up once/day.

It seems that the easiest way to move the databases is to just restore from the .bak files on the new server. I've tested that this works, aside from the fact the logs of the restored databases are locked waiting for a replication that no longer exists.

This query:

USE master;
GO

SELECT
    DB.name [DATABASE],
    DB.recovery_model_desc [RECOVERY MODE],
    DB.log_reuse_wait_desc [LOG WAITING ON],
    CAST(CAST(MF.size AS decimal) * 8 / 1024 / 1024 AS DECIMAL(10,2)) [FILE SIZE GB],
    CASE UPPER(REVERSE(SUBSTRING(REVERSE(MF.physical_name), 0, 4))) WHEN 'MDF' THEN 'DATA' WHEN 'LDF' THEN 'LOG' ELSE NULL END [FILE TYPE],
    UPPER(REVERSE(SUBSTRING(REVERSE(MF.physical_name), 0, 4))) [FILE EXTENSION]
FROM sys.databases DB
INNER JOIN sys.master_files MF ON MF.database_id = DB.database_id
ORDER BY
    [FILE TYPE] DESC,
    [FILE SIZE GB] DESC

produces LOG WAITING ON = REPLICATION for the restored databases.

Since the replication no longer exists, I can't just delete the publications on the new server.

Question. Do I need to delete the snapshot replication on the databases before moving them across? Or can I change this log_reuse_wait status to NOTHING manually?

Some more detail:

Server A has replication enabled. DB A.a is marked for snapshot replication. This rep is run occasionally. Database A.a is in SIMPLE recovery mode, with a full backup taken nightly.

Server B is new and doesn't have replication enabled yet. I have found when I restore A.a to B.a, the restoration works. But the log file is still being held for replication.

I would like the log file on B.a to NOT be marked for replication...

score 3 · Accepted Answer · edited Oct 13 '17 at 07:31

On the Q & A Transaction Log won'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).

How do you manually change the 'log_reuse_wait' status in SQL Server?

1 Answers1