log_reuse_wait_desc = replication but there's no replication

Question

log_reuse_wait_desc = replication but there's no replication

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Rob Mahaney SSC Veteran Points: 288 More actions · Answer 1

One other way to reset a tran log for a restored database that is showing REPLICATION and won't shrink is to detach the database, rename or delete the log file and reattach.

[/url]

cjmorgan SSC Enthusiast Points: 181 More actions · Answer 2

So I just ran into this same thing and did what Gail stated to do. But I am totally baffled as this database was NEVER involved in replication either on the old server (SQL 2008 R2) that it resided on or the new server (SQL 2016). Neither server was EVER setup for replication but all of a sudden at 2:18 am PDT this morning (11/4/17) I started receiving a flood of Severity 020 errors:
The log scan number (5423817:3214:0) passed to log scan in database '<DatabaseName>' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file(.mdf). If this error occurred during replication, re-create the publication.Otherwise, restore from backup if the problem results in a failure during startup.

SQL was not recycled, and unfortunately the error log got so bloated with over 2000 of these that it won't load in memory. I just wish I knew what triggered the database to think it was all of a sudden part of replication. FYI, CDC was/is not turned on for this DB.

Fixed (from Gail)
Set the server up for replication, create a publication in the DB in question. Publish any table (not important which one) and then completely remove replication again.

cjmorgan SSC Enthusiast Points: 181 More actions · Answer 3

Just as a warning, this issue may come back after database has been working for some time. We just had to repair a databases that initially was on a server that had Replication/Distribution setup on it (but not for the database we were having the issue for) and when we backed up/restored it to a new server (that had no replication setup) this error popped up at that time (over a year ago). Recently we had to repair the database and less than 24 hours later, this error popped up again. SAme log_reuse_desc in Sys.databases (replication).
Setup replication/publication which changed the wait to a log backup, then removed the publication/replication and all good again. So if this does come along again, don't panic, check the Log_reuse_desc column in sys.databases and see what it says and follow the above posts.
Message:
DESCRIPTION: The log scan number (5583766:93420:2) passed to log scan in database '<database name here>' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

CJ Morgan

naim.sulejmani Valued Member Points: 73 More actions · Answer 4

thx you (Y), after 9 years still you saved our production server

naim.sulejmani Valued Member Points: 73 More actions · Answer 5

thx you (Y), after 9 years still you saved our production server

fonck Newbie Points: 1 More actions · Answer 6

Many thanks friends!!!

More than 10 years have passed and this post is still useful.

You guys helped me save a server that we still can't migrate. (OS Win Server 2003 with SQL Server 2005).

Greetings from Chile!