August 15, 2008 at 9:41 am
I have an interesting problem with one database on a production server. Noticed the TLOG % Used was not decreasing on log backups and was continually increasing. Upon further investigation which was a DBCC OPEN TRAN and SELECT log_reuse_wait_desc,NAME,* FROM sys.databases, it became apparent that the DB thinks it is participating in replication. Well, replication has never been setup on the server or on any of our production servers for that matter.
My research indicates this was a problem back around RTM and SP1 was supposed to fix this quirk. Well, i am running SP2 + some hotfixes.
I am experienced DBA but have never used replication. i need to know how I can get SQL Server to "reset" the flag on this DB without taking down a production DB.
Would a exec sp_removedbreplication 'mydb' do it?
Any help would be appreciated.
August 15, 2008 at 9:44 am
Also, in case it matters for the solution, the replication components were not even installed for this instance. I do see replication system stored procs in master though. They must be created regardless.
Paul
August 15, 2008 at 10:52 am
Would a exec sp_removedbreplication 'mydb' do it?
Yes! that should solve your problem.
* Noel
August 15, 2008 at 11:10 am
I have run into the same situation and most of the time the sp_removedbreplication command takes care of it.
However, on rare occasions the SPROC is not able to remove the replication flag within the database, and I found that the following script takes care of it:
sp_dboption 'mydb', 'published', 'true'
GO
USE mydb
GO
sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
GO
sp_dboption 'mydb', 'published', 'false'
GO
You may need to enable replication on that server temporarily in order for this script to properly run.
Matt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply