Solved

stop replication

Posted on 2006-06-12
17
2,111 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
Comment Utility
0
 
LVL 21

Assisted Solution

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

Author Comment

by:TRACEYMARY
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now