Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

stop replication

Posted on 2006-06-12
17
Medium Priority
?
2,146 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 400 total points
ID: 16887035
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 800 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 800 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 400 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 400 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

916 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