stop replication

Any one know how to stop the replication service.
I see the dts jobs and on replication publications right clicked subscription name and did stop

but still table is locked.
LVL 7
TRACEYMARYAsked:
Who is Participating?
 
Kevin3NFConnect With a Mentor Commented:
what type of replication....snapshot, transactional or merge?

Messing with replication without understanding can create real issues.....please be cautious

Have a look at this in Books Online:
sp_droparticle
Drops an article from a snapshot or transactional publication. An article cannot be removed if one or more subscriptions to it exist. This stored procedure is executed at the Publisher on the publication database.


removing a table from your publication requires that you drop the subscriptions (which will require that you re-initialize them later)
0
 
Kevin3NFConnect With a Mentor Commented:
Look on the Distribution server in the "replication Monitor" folder.  Drill down to the agents.

Look for log reader, distribution and snapshot agents.  Log reader and snapshot are the ones typically responsible for locking.

What sort of replication are you using?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
TRACEYMARYAuthor Commented:
Got snapshot, log reader, two push agents  all say stopped...except log reader still green...but stopped that.

But the TABLE is still locked by replication as i cannot drop it.

I believe its the merge replication.

0
 
TRACEYMARYAuthor Commented:
Reading the article.

Where do i go first we have replication from SQL1   two agents on SQL 1

I stopped everything in Distribution server in the "replication Monitor" folder
0
 
TRACEYMARYAuthor Commented:
All i need to do is just stop it and remove one table from the replication....not remove it completely.
0
 
TRACEYMARYAuthor Commented:
snapshot.........we use.

Our other guy is not intoday and i need to run a script but the replication has my table...
Our entire application is down .....

Any help im on my own here.

0
 
TRACEYMARYAuthor Commented:
Can i just unflagged the table in the replicaiton monitor, publications, filter columns.
just in here drop selected column will that release the table.

0
 
LowfatspreadCommented:
what are you trying to do to the table?

if the table you're trying to access the publisher or subscriber?

which version and edition of sql server?
0
 
TRACEYMARYAuthor Commented:
I have one table in our database that i need to drop and recreate...but when i do drop it gives me used by replication.

So i need to remove the table in replication process.
I went to the replication monitor, publishers...i see filter columns but i cannot see how to remove this table from the replication.

I have stopped the replications but table is still locked.
0
 
LowfatspreadConnect With a Mentor Commented:
to "stop" replication you need to drop the publication

HOWEVER ... if you are involved in MERGE replication then its going to get messy....!

Why do you think you need to drop and recreate this table?

Looking back you did say merge...

Have you considered what data is being exchanged between the two databases...?
what is going to be the effect of losing this data...?






0
 
Kevin3NFCommented:
Snapshot locks the entire table by default.

I cannot in good conscience tell you how to take apart replication without knowing your specific situation far better than is possible on an internet forum.

I was on the replication support team at MIcrosoft, and this call would me scrambling to make sure EVERYTHING was scripted out and backed up.

You may want to call in on this one....$245 to get the whole application back up ain't a bad price....

Alternatively, if you just need to add or drop a column, look at sp_repldropcolumn and sp_repladdcolumn in BOL...
0
 
LowfatspreadCommented:
>sp_repldropcolumn and sp_repladdcolumn

depends on your version of SQL Server...

I agree with Kevin ... this is not a trivial area..
if you've decide to replicate that basically means you'ved decided to not allow modification to
the tables involved....

wait until your colleague returns...

0
 
TRACEYMARYAuthor Commented:
I just deleted the subscriptions..............and it has released the table.

Tomorrow i put the subscriptions back.......................gosh what a nightmare....
I have to figure out how to do replication that does not lock my tables.....
0
 
Kevin3NFCommented:
Snapshot will always lock the tables when it runs.  The answer there is to not run it during the day

Transaction and Merge lock when they are reading and writing to the tables...nothing you can do about that, other than be comforted by the fact that the locks are generally not at the table level
0
 
lokeshgm7Connect With a Mentor Commented:
The easiest way to do this would be to disable replication (Since its a snapshot replication, it shouldnt be a big deal to re-setup the replication), do the required modification on the table and recreate the replication. Check this article on how to disable replication manually. Its very simple to follow.

http://support.microsoft.com/?id=324401

0
 
TRACEYMARYAuthor Commented:
I did stop all the replication and the snapshot still held my table.....
when you say modification on table...i still could not modify it...i had to delete the subscriptions...completely then something in the alert button allowed me to remove the table.....

Thanks all.......................

Today i work on rebuilding it..........that be fun.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.