log_reuse_wait_desc = replication but there's no replication

  • 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)

  • 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
  • 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)

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

    Happy new year

  • 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!

  • Thanks for the tip Gail!

  • Thank you..:)

  • 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.

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

  • 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?

  • waow!!!...this worked like charm

  • it worked 🙂

  • Thanks a lot!:-)

  • 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%.

  • 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