log_reuse_wait_desc = replication but there's no replication

Question

log_reuse_wait_desc = replication but there's no replication

stephen.hendricks

SSC Eights!

Points: 845
More actions
April 10, 2009 at 12:41 pm

#208063

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)

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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

Happy new year

michael_baker SSC-Addicted Points: 490 More actions · Answer 4

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: 20443 More actions · Answer 5

Ignacio A. Salom Rangel

SSC-Insane

Points: 20443

August 17, 2011 at 2:02 pm

#1369833

Thanks for the tip Gail!

My blog

Achilies SSCommitted Points: 1695 More actions · Answer 6

Achilies

SSCommitted

Points: 1695

August 17, 2011 at 2:52 pm

#1369867

Thank you..:)

subramanianks SSC Enthusiast Points: 101 More actions · Answer 7

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 More actions · Answer 8

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

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 9

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?

My blog

teavch SSC Enthusiast Points: 118 More actions · Answer 10

teavch

SSC Enthusiast

Points: 118

June 26, 2013 at 5:10 am

#1627469

waow!!!...this worked like charm

teavch SSC Enthusiast Points: 118 More actions · Answer 11

teavch

SSC Enthusiast

Points: 118

June 26, 2013 at 5:12 am

#1627472

it worked 🙂

chriskluczyk Grasshopper Points: 17 More actions · Answer 12

chriskluczyk

Grasshopper

Points: 17

August 15, 2013 at 8:30 pm

#1641968

Thanks a lot!:-)

Makarand Mohandas Right there with Babe Points: 783 More actions · Answer 13

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: 2754 More actions · Answer 14

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)