log_reuse_wait_desc = replication but there's no replication

  • Rob Mahaney

    SSC Veteran

    Points: 268

    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

    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

    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

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

  • naim.sulejmani

    Valued Member

    Points: 73

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

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

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