Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

log_reuse_wait_desc = replication but there's no replication Expand / Collapse
Author
Message
Posted Friday, April 10, 2009 12:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 7, 2009 5:46 PM
Points: 295, Visits: 67
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)
Post #695034
Posted Friday, April 10, 2009 1:11 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
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 2008, MVP
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

Post #695043
Posted Friday, April 10, 2009 1:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 7, 2009 5:46 PM
Points: 295, Visits: 67
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)
Post #695058
Posted Wednesday, January 5, 2011 4:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 6:42 AM
Points: 2, Visits: 66
1 year later... but thank again. Today you save my production server......

Happy new year
Post #1042932
Posted Wednesday, January 12, 2011 8:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:14 PM
Points: 4, Visits: 74
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!
Post #1046961
Posted Wednesday, August 17, 2011 2:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:55 PM
Points: 3,084, Visits: 1,431
Thanks for the tip Gail!




My blog
Post #1161417
Posted Wednesday, August 17, 2011 2:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:55 PM
Points: 244, Visits: 435
Thank you..:)
Post #1161456
Posted Wednesday, June 27, 2012 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 12:00 PM
Points: 1, Visits: 44
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.
Post #1322001
Posted Saturday, October 27, 2012 5:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 12:34 PM
Points: 1, Visits: 33
But it still showing Replication in the log_reuse_wait_desc column, Please help
Post #1377943
Posted Saturday, October 27, 2012 12:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:55 PM
Points: 3,084, Visits: 1,431
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
Post #1377968
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse