June 21, 2009 at 4:54 am
Yes I'm using snapshot replication, and manually ran the jobs required to test it was sending data across correctly. I scheduled these jobs to run once a day over-night.
Here's the screen-print of what you were after Gail:
June 21, 2009 at 5:08 am
Right. That's snapshot replication. No sign of transactional replication. (it has a different icon for the publication)
Since snapshot replication doesn't use the tran log, running sp_repldone would not have broken it. Running it will have temporarily solved the log problem but, since transactions going in the log are still getting marked for replication and there's no log reader (only present in transactional replication), eventually you'll be right back where you started, massive log file and replication as a reuse wait description.
I'm not sure all the possible causes for this. The one I'm most familiar with is restoring a published database to a server with no replication. Doing that leaves a half-published database with exactly this symptoms (I used to have this at the previous company that I worked for, so we got to do this replication fix every couple weeks). I've also seen it when someone created a publication, tried to remove it but encountered an error.
One of the permanent fixes for this is as follows:
Create a transactional replication publication in this database.
Add a article, it doesn't matter at all what table you decide to use.
Finish the wizard, but don't let SQL create the snapshot. There's no need.
Delete the publication.
It sounds simple, but deleting the publication when there are no other transactional replication publications will let SQL clean the database up properly and remove any traces of transactional replication, including removing the need to mark new transactions as to be replicated.
After doing this, run DBCC opentran. You should now not see any reference to replicated or non-replicated transactions. Those only appear in a DB where there's some trace of transactional replication.
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
June 21, 2009 at 5:12 am
I see, thanks for taking the time to explain Gail.
This almost seems like a problem with SQL Server then in what I've experienced :crazy:
June 21, 2009 at 5:32 am
JamesNZ (6/21/2009)
This almost seems like a problem with SQL Server then in what I've experienced :crazy:
It's not a bug, if that's what you're asking. In 99% of the cases I've seen of this it's because a published database is backed up and restored to a server without replication configured. Bear in mind that restore restores the DB exactly as it was at the time of backup.
There's probably an option somewhere in the backup/restore, though not one I've bothered to look for or know about.
How it happened on your server, I can't tell you, not without spending time investigating it.
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
June 21, 2009 at 5:50 am
Thanks Gail.
June 21, 2009 at 6:10 am
Thanks Gail for detailed explaination. It certainly helped a lot.:-)
James, As suggested by Gail, you'll need to perform those steps(to rebuild transaction replication and delete it) for permanently fixing this up or else this situation will arise sooner or later.
June 21, 2009 at 6:13 am
Thanks Pradeep, appreciate the help 🙂
Viewing 7 posts - 61 through 66 (of 66 total)
You must be logged in to reply to this topic. Login to reply
