March 12, 2010 at 3:08 am
Hi all
Currently we have a snapshot replication setup which takes a fresh snapshot every night. The other day one of the devs was doing a release and ended up taking the DB offline (I know I know)... anyway what we ended up with was a bloated transaction log as it seemed when I ran DBCC OPENTRAN that a replication transaction was marked as open.
I managed to resolve this by using the sp_repldone proc. But this got me thinking. If we were using transactional\merge replication then we would have lost that transaction so what is the best way to gracefully stop an instance using replication to allow a release to happen.
My current thinking is to place the instance in Single user mode, stop any of the replication agents and then allow the release to happen before bringing it all back up.
Any comments are welcome.
Thanks
March 12, 2010 at 7:05 am
You just have to stop the SnapShot agent in your case since you have snapshot replication running.
-Roy
March 12, 2010 at 8:29 am
Kwisatz78 (3/12/2010)
Hi allCurrently we have a snapshot replication setup which takes a fresh snapshot every night. The other day one of the devs was doing a release and ended up taking the DB offline (I know I know)... anyway what we ended up with was a bloated transaction log as it seemed when I ran DBCC OPENTRAN that a replication transaction was marked as open.
Who runs code for production release? Is developers allowe to make schema changes in prod with SQL statements? You should make sure no changes is made when snap shot is running.
I managed to resolve this by using the sp_repldone proc. But this got me thinking. If we were using transactional\merge replication then we would have lost that transaction so what is the best way to gracefully stop an instance using replication to allow a release to happen.
You won't lose any transaction as replication engine understands internally if it is in middlw of the transaction it will just roll back, why do you want to stop instance for replication changes?
My current thinking is to place the instance in Single user mode, stop any of the replication agents and then allow the release to happen before bringing it all back up.
Check Schema changes option, Because you have snapshot replication just take articles out of publisher which are involved in schema changes and allow release, once release is complete add article back to publisher and apply fresh snap shot.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply