August 13, 2014 at 3:30 pm
Hello,
I have (or had) Transactional Replication configured, from one SQL 2008R2 cluster to a SQL 200R2 stand alone (let's call it Report Server). We are replicating tons of articles, but not the entire databses... and the snapshot is about 100 GB
My foes started when I noticed that the susbcriptions were expired. then when I tried to re-initialized the snapshot...
they started having severe deadlocks... so I was asked to drop the replication.
I tried... and it crap out.
Whenever I run it takes forver... I let it run overnight for 10 hours, so I'm not sure if it's doing anything.
exec sp_removedbreplication
I'm lucky enough to had set up the same environment on a newer set of servers (we are migrating there in a few months), so I can compare what I see there over here, and I noticed that in the working environment there are tons of system tables that on my current Prod environment are gone.
I guess that means that some of my replication went away..
This is a push replication, and I don't see anything under the replication folder on SSMS
But when I run the following commands:
exec sp_helpreplicationdb
it shows that the database is still a publisher.
and when I run
Select databaseEx (@dbname,'isPublished')
it still says 1. meaning, yes.
I've look all over SQLCentral, and SQLSkills, google, etc, and I can't figure out how to nuke it.
All resources tell me to run sp_removedbreplication, but that's not cutting it. I'm tired and I can't think anymore...
Any advise?
Another thing that I found was to "simple" "Disable Publishing and Distribution", but then I get errors sus as "invalid object name dbo.syssubscriptions" (duh... I already remove them! )
and that
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Pervious count = 0 , current count = 1
August 13, 2014 at 4:52 pm
Did you run all the steps outlined in this article?
http://technet.microsoft.com/en-us/library/ms147921(v=sql.105).aspx
August 13, 2014 at 7:41 pm
Yes, however, step three is
At the Publisher on the publication database, execute sp_removedbreplication to remove replication objects from the database.
And that's the problem... It runs and run and I'm not sure it finishes. I let it run overnight for like 10 hours before stopping it.
August 13, 2014 at 8:00 pm
holy camoly!
I found the problem!
The key was that I stopped it. I stopped/cancel the sp_removeDBReplication
which if we couple with the other error:
"Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0 , current count ="
It all makes sense. There was a missing commit because I stop it!.
so I simply stop SQL, restart it, and then run exec sp_removedbreplication and it worked like a charm!
Thank you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply