Solved

stop replication

Posted on 2006-06-12
17
2,117 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:TRACEYMARY
  • 8
  • 4
  • 3
  • +2
17 Comments
 
LVL 20

Assisted Solution

by:Sirees
Sirees earned 100 total points
ID: 16887035
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 200 total points
ID: 16887085
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16887135
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16887151
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16887178
All i need to do is just stop it and remove one table from the replication....not remove it completely.
0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 200 total points
ID: 16887196
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16887246
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16887294
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16887499
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16887622
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 16887825
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 16887829
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16888079
>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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16888108
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 16888385
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
 
LVL 3

Assisted Solution

by:lokeshgm7
lokeshgm7 earned 100 total points
ID: 16892256
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16892838
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question