log_reuse_wait_desc = replication but there's no replication

  • stephen.hendricks

    SSC Eights!

    Points: 845

    I have a database whose log file keeps growing. I checked the sys.databases log_reuse_wait_desc column and it shows "replication" as the reason the log won't allow shinking. The problem is that there is no replication (publisher or subscriber) on the database or on the server.

    Does anyone have some insight as to how to resolve this or how it might have arisen in the first place?

    ============================================================
    I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Either they were restored from backups of published databases or there was replication at one point and it was removed improperly.

    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.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • stephen.hendricks

    SSC Eights!

    Points: 845

    Thank-you, Gail. you were exactly right that this was a restore of another server's database. I ran the publish/remove publish and all is well again. Not bad for a Friday.

    Thanks, again.

    ============================================================
    I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

  • gasparini domenico

    SSC Enthusiast

    Points: 178

    1 year later... but thank again. Today you save my production server......

    Happy new year

  • michael_baker

    Old Hand

    Points: 350

    Thank you, thank you, thank you! Here was my command for our MSDB log file going out of control. After determining that our Simple recovery model database was acting like a full rcovery db:

    SELECT name, log_reuse_wait_desc FROM sys.databases

    log_reuse_wait_desc = Replication

    I forced removal of the replication (must have been caused during a restore at some point).

    EXEC sp_removedbreplication msdb

    Reran the following:

    SELECT name, log_reuse_wait_desc FROM sys.databases

    Forced to Simple Recovery (just to make sure):

    ALTER DATABASE msdb SET RECOVERY SIMPLE

    Ran standard shrink commands (found all over google or our favorite search engine 😉 )

    backup log msdb with truncate_only

    dbcc shrinkfile(MSDBLog)

    Chicka chicka boom boom...now there's enough room!

  • Ignacio A. Salom Rangel

    SSC-Insane

    Points: 20382

    Thanks for the tip Gail!

  • Achilies

    SSCommitted

    Points: 1549

    Thank you..:)

  • subramanianks

    SSC Enthusiast

    Points: 101

    Thanks! I had a similar issue - the log file was not getting reused even though the recovery mode was set to SIMPLE, and the log_reuse_wait_desc reason showed 'replication' even though there was no replication set up for the db (verified thru DATABASEPROPERTYEX ( db_name() , 'IsPublished' ), which returned 0). To resolve the problem, I tried "sp_repldone null, null, 0,0,1" first, but that just gave an error message that the db is not marked as being published. Then tried sp_removedbreplication, and that worked like a charm.

  • asksubir

    SSC Enthusiast

    Points: 181

    But it still showing Replication in the log_reuse_wait_desc column, Please help

  • Ignacio A. Salom Rangel

    SSC-Insane

    Points: 20382

    asksubir (10/27/2012)


    But it still showing Replication in the log_reuse_wait_desc column, Please help

    Which steps have you follow so far?

  • teavch

    SSC Enthusiast

    Points: 118

    waow!!!...this worked like charm

  • teavch

    SSC Enthusiast

    Points: 118

    it worked 🙂

  • chriskluczyk

    Grasshopper

    Points: 17

    Thanks a lot!:-)

  • Makarand Mohandas

    Right there with Babe

    Points: 783

    I saw this on my SQL server 2008 R2.

    I tried removing the replication using "EXEC sp_removedbreplication" but it did not work. In fact I never had replication enabled, althugh there was a plan to do so over the previous weekend. unless some guy in the team tried to set it up and did not clean up properly. He shouldnt be doing this in prod systems

    anyways... I ran a checkpoint. My log usage was 95% of 122 GB. It came down to 0.25%.

  • Shafat Husain

    SSCrazy

    Points: 2744

    Hi,

    Found a wonderful article on this topic.

    # http://blogs.msdn.com/b/repltalk/archive/2010/11/17/how-to-cleanup-replication-bits.aspx

    Hope it helps..!!

    Regards

    Shafat Husain

    Regards,
    Shafat Husain
    🙂
    And Your Lord Never Forgets...!! (64:19 -- Quran)

Viewing 15 posts - 1 through 15 (of 35 total)

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