|
|
|
SSC Veteran
 
Group: General Forum Members
Last Login: Friday, August 07, 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)
|
|
|
|
|
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 30,547,
Visits: 23,268
|
|
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
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
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Friday, August 07, 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)
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 2,
Visits: 49
|
|
1 year later... but thank again. Today you save my production server......
Happy new year
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, October 10, 2011 2:45 PM
Points: 2,
Visits: 35
|
|
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!
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:11 PM
Points: 2,746,
Visits: 1,106
|
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, February 17, 2012 8:43 AM
Points: 125,
Visits: 184
|
|
|
|
|
