deleting restored dB that is read-only

I restored a dB and set it as read-only. The dB is being
used for replication, so in my Replication Monitor in EP,
all the publications are listed numerous times. I cannot
detach the dB because it is being used for replication.
I cannot use sp_removedbreplication because it is read-
only. So the question is, how do I remove the dB? How
do I change the read-only flag off?
Larry,
this should remove the database for you (just need to replace xxx with your
database name).
alter database xxx set read_write with rollback immediate
go
exec sp_removedbreplication xxx
go
use master
go
drop database xxx
go
Regards,
Paul Ibison
|||Paul,
I ran the commands you suggested and received the
following errors...
Server: Msg 5063, Level 16, State 1, Line 1
Database 'StoreMain-lpr10' is in warm standby. A warm-
standby database is read-only.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 3906, Level 16, State 1, Procedure
sp_dropsubscription, Line 441
Could not run BEGIN TRANSACTION in database 'StoreMain-
lpr10' because the database is read-only.
Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database 'StoreMain-lpr10' because it is
being used for replication.
HELP!!!
Larry...
|||Larry,
this database is in RO mode because it has been restored without recovery.
The following script should be run before my original one:
alter database StoreMain-lpr10 set single_user with rollback immediate
go
restore database StoreMain-lpr10 with recovery
go
Regards,
Paul Ibison

Deleting replication, leaves data in distribution database

Hi,

>From a publisher I replicate 3 databases to a distributor/subscriber
machine. On all of them i have MS SQL 2005.
When i delete one of the replications by running
publisher => sp_dropsubscription
subscriber/distributor=>sp_removedbreplication
publisher => sp_removedbreplication
subscriber/distributor =>sp_subscription_cleanup
On the publisher and subscriptions databases all replication things
are removed.
However when looking with the replication monitor the deleted
replication is visible with a big red cross. Also the jobs are still
in the job list of the distributor!
When removing everything by dropping the distribution database all is
cleared. I know that in SQL 2000 the distributor would also be
cleared. Has this changed?
Is this a bug in SQL2005 or am i forgotting something (i have searched
a lot already, but cannot find anything)?
Hopefully someone can help me?
Marcel
I'd recommend using sp_droppublication which'll clean up the jobs as well.
sp_removedbreplication is something I run only occasionally to clean up any
remaining orphaned objects.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||On 1 feb, 10:28, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I'd recommend using sp_droppublication which'll clean up the jobs as well.
> sp_removedbreplication is something I run only occasionally to clean up any
> remaining orphaned objects.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
Thanks Paul,
This looks much better! I finalized with
EXEC sp_replicationdboption
@.dbname = @.publicationDB,
@.optname = N'publish',
@.value = N'false';
This should remove the publication objects...
Marcel

Deleting records from merge replication

I have a merge replication set up.
I need to delete some records at publisher which i don't want to be
replicated.
What should i do?
Thank you.
I suppose you could temporarily disable the merge triggers, but I really
wouldn't recommend this. If changes are made meanwhile, or if changes are
made to the subscriber rows you're keeping, there will be failures later on.
What I'd do is archive off these rows on the subscriber then do the delete.
You can amalgamate the rows (union) for the client application if necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have the same issue. I want to delete old date but hate to clog up
replication with 500K deletes. A user may have to synch for days to get thru
them.
Untested idea yet but I was considering pausing the merge agent job,
deleting the rows, isolating the deletes in msmerge_tombstone and genhistory
and deleting those specific rows. Restarting the agent would make the big
deletes get ignored , right?
Then run a similar script on the subscriber to trim the size too.
Anyone speculate on any unforseen ill effects? would it throw generation
out of whack?
Mike
If data falls in the woods and nobody is there to see it ...... ?
"ravi lobo" wrote:

> I have a merge replication set up.
> I need to delete some records at publisher which i don't want to be
> replicated.
> What should i do?
> Thank you.
|||I can not disable DELETE trigger because as paul said i may some of the
deletes fired on the subscriber.
Tigermikefl,
I have tried your solution twice some time back. One time it worked. Failed
at the second time!
So i was forced to recreate the replication ! (It was very painful)
gen history table created some problem.
So i will not use your method unless somebody test it full-proff!
Also i need these functionality on a scheduled basis, stopping merge agent
followed by deleting tombstone will be painful.
|||You can change this behavior on the article level. What you need to do is
create an account in the PAL and limit the permissions on the publisher on
an as needed basis. Suppose the PAL account your particular subscriber is
pulling with is called Ravi. Grant select and update permissions to Ravi on
the table in the publisher - we'll call it raviTable.
Now right click on your publication, select publication properties, and
select the articles tab. Click on RaviTable, and click the browse button,
select Merging Changes, ensure that Delete is selected. Now what will
happen is that deletes will occur on the subscriber, and be replicated to
the publisher but be kicked back as conflicts. By default these deletes will
remain in the subscriber (IIRC) even with the compensate_For_errors setting
set to true (the default).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>I have a merge replication set up.
> I need to delete some records at publisher which i don't want to be
> replicated.
> What should i do?
> Thank you.
|||Hilary,
Thank you for the reply.
I think the solution given by you prevents the DELETES at the subscriber
to replicate to the publisher.
I have a huge number of records which needs to be deleted. I am afraid
if I use your approach it will clog the n/w.
Infact I need to delete the records at the publisher.
Any suggestions?
"Hilary Cotter" wrote:

> You can change this behavior on the article level. What you need to do is
> create an account in the PAL and limit the permissions on the publisher on
> an as needed basis. Suppose the PAL account your particular subscriber is
> pulling with is called Ravi. Grant select and update permissions to Ravi on
> the table in the publisher - we'll call it raviTable.
> Now right click on your publication, select publication properties, and
> select the articles tab. Click on RaviTable, and click the browse button,
> select Merging Changes, ensure that Delete is selected. Now what will
> happen is that deletes will occur on the subscriber, and be replicated to
> the publisher but be kicked back as conflicts. By default these deletes will
> remain in the subscriber (IIRC) even with the compensate_For_errors setting
> set to true (the default).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>
>
|||The only suggestion I can make is to set ExchangeType to 1 (upload only),
but this will prevent all transactions from moving from the publisher to the
subscriber. You could drop the subscription, do the delete, and then
reinitialize, but I doubt this will work for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:EA14635C-0A17-416F-B800-DD30A9EEA65B@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thank you for the reply.
> I think the solution given by you prevents the DELETES at the subscriber
> to replicate to the publisher.
> I have a huge number of records which needs to be deleted. I am afraid
> if I use your approach it will clog the n/w.
> Infact I need to delete the records at the publisher.
> Any suggestions?
> --
> "Hilary Cotter" wrote:
|||I can not use ExchangeType to 1,but can use dropping the subscription in the
green zone. Just wanted to avoid that.
Thank you Hilary, paul and Tigermikefl.
Good to know all those options.
"Hilary Cotter" wrote:

> The only suggestion I can make is to set ExchangeType to 1 (upload only),
> but this will prevent all transactions from moving from the publisher to the
> subscriber. You could drop the subscription, do the delete, and then
> reinitialize, but I doubt this will work for you.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:EA14635C-0A17-416F-B800-DD30A9EEA65B@.microsoft.com...
>
>
|||Yes, that can be made to work and I have been through that process before.
No, I won't post scripts or the exact steps to follow. You are playing with
internal metadata the merge engine uses. The engine only moves what is in
the metadata tables, if it isn't there, it dosn't know about it. So, if you
remove the wrong row, you will forever be out of synch with the only option
being to reinitialize. They we'd have to hear about why merge isn't
working. If you understand the internal structures behind merge and exactly
how it moves data around the system, you already know how to do this and
don't need it explained. It is absolutely unsupported and very definitely
not recommended.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:A9500349-5C6B-4B61-919A-ADD05A4C3315@.microsoft.com...[vbcol=seagreen]
>I have the same issue. I want to delete old date but hate to clog up
> replication with 500K deletes. A user may have to synch for days to get
> thru
> them.
> Untested idea yet but I was considering pausing the merge agent job,
> deleting the rows, isolating the deletes in msmerge_tombstone and
> genhistory
> and deleting those specific rows. Restarting the agent would make the big
> deletes get ignored , right?
> Then run a similar script on the subscriber to trim the size too.
> Anyone speculate on any unforseen ill effects? would it throw generation
> out of whack?
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "ravi lobo" wrote:
|||And then you will have 100% of the deletes flow down to the subscriber as
well as 1 row for each row deleted flow back up to the publisher to be
logged into the conflict tables. You would then have to go into the
conflict table and clean out potentially thousands of entries. There is no
way I would recommend doing this.
If you need to do something like this, you need to find a maintenance window
when nothing else is being deleted. Then disable the delete trigger on the
publisher, issue your deletes, and reenable the trigger. The delete will
occur on the publisher, but since the trigger was disabled at that time,
there will be no delete logging for the merge engine to move. You will have
to remember to disable all of your validation scripts after this, because
the system will never validate successfully from that point forward.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23fRI%239sFGHA.644@.TK2MSFTNGP09.phx.gbl...
> You can change this behavior on the article level. What you need to do is
> create an account in the PAL and limit the permissions on the publisher on
> an as needed basis. Suppose the PAL account your particular subscriber is
> pulling with is called Ravi. Grant select and update permissions to Ravi
> on the table in the publisher - we'll call it raviTable.
> Now right click on your publication, select publication properties, and
> select the articles tab. Click on RaviTable, and click the browse button,
> select Merging Changes, ensure that Delete is selected. Now what will
> happen is that deletes will occur on the subscriber, and be replicated to
> the publisher but be kicked back as conflicts. By default these deletes
> will remain in the subscriber (IIRC) even with the compensate_For_errors
> setting set to true (the default).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:DBE8C6C1-82FF-4704-8962-162175CE9F66@.microsoft.com...
>

deleting publications

Hi,
I created a few push subscriptions in continuous mode for testing
merge replication. I stopped the synchronization before I tried to
delete the publication. I got an error stating, can't delete because
the publication is being used for replication. I can't delete the
subscriptions on the subscriber as well. The tables associated with
the publications also can't be deleted. I don't want to use the Wizard
to delete all the publications.
Does anyone know how I can delete the publications?
Thanks!
PS.
PS,
try using sp_dropmergesubscription for each subscription, followed by
sp_dropmergepublication. Please report back the exact error message if there
is one.
When we can get the publication deleted: to ultimately drop the table you
can use a stored procedure to do this called sp_MSunmarkreplinfo which takes
a tablename as a parameter. Alternatively, running sp_removedbreplication
can be used to remove all traces of replication in the subscriber database,
but obviously must only be done if this database is not also configured as a
publisher.
HTH,
Paul Ibison
|||Hi,
I tried what you suggested and I got rid of the subscriptions and
publications.This is what I did:
I stopped synchronization, and deleted the publication entry under
Replication.
I then called sp_MSunmarkreplinfo to delete the table used for
replication.
On the subscriber however,
I had to delete the entries from 'sysmergearticles' and
'sysmergepublications' in order to delete the subscription entry under
'replication->subscriptions' and then call sp_MSunmarkreplinfo' to
delete the table.
Thanks for the help!
PS.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<urzlrrQVEHA.584@.TK2MSFTNGP09.phx.gbl>...
> PS,
> try using sp_dropmergesubscription for each subscription, followed by
> sp_dropmergepublication. Please report back the exact error message if there
> is one.
> When we can get the publication deleted: to ultimately drop the table you
> can use a stored procedure to do this called sp_MSunmarkreplinfo which takes
> a tablename as a parameter. Alternatively, running sp_removedbreplication
> can be used to remove all traces of replication in the subscriber database,
> but obviously must only be done if this database is not also configured as a
> publisher.
> HTH,
> Paul Ibison

Deleting Large Quantiy of Rows

Hi Guys,
I need a little advice. I need to delete 250000 rows on a monthly basis and
my users are using MSDE 2000 with merge replication, publisher is SQL Server
2000 Standard.
The users will never be uploading or making changes to this table. Is there
any way i can make this a quick delete as currently for each row that is in
the Article published table sql is sending down a delete statement for each
row to the subscriber. Thus taking longer than expected.
Any ideas or advice would be greatly apprecitaed.
Thanks, Tim.
From what you are saying it looks like these rows on the subscriber are read
only and the data flow is only from the publisher to the subscriber for this
table. If so remove this table from the merge publication, put it in a
transactional publication and do your deletes through a stored procedure and
replicate the execution of the stored procedure.
Hilary Cotter
Looking for a SQL Server replication book
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> Hi Guys,
> I need a little advice. I need to delete 250000 rows on a monthly basis
and
> my users are using MSDE 2000 with merge replication, publisher is SQL
Server
> 2000 Standard.
> The users will never be uploading or making changes to this table. Is
there
> any way i can make this a quick delete as currently for each row that is
in
> the Article published table sql is sending down a delete statement for
each
> row to the subscriber. Thus taking longer than expected.
> Any ideas or advice would be greatly apprecitaed.
> Thanks, Tim.
>
|||Hi Hilary,
Thanks for your response, this is not going to be possible as we use the
ActiveX control and the publications have been hard coded. Which i know is
not good. Is there any other way that we could perhaps specify the order
that things get run in I.E sp_AddScriptexec. I have worked a way out with
the system tables that we could remove generations. However i would need to
make sure that sp_addscriptexec was fired first in the replication process.
Thanks, Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
> From what you are saying it looks like these rows on the subscriber are
> read
> only and the data flow is only from the publisher to the subscriber for
> this
> table. If so remove this table from the merge publication, put it in a
> transactional publication and do your deletes through a stored procedure
> and
> replicate the execution of the stored procedure.
> --
> Hilary Cotter
> Looking for a SQL Server replication book
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> and
> Server
> there
> in
> each
>
|||I think your best option would be to drop the subscription (possibly drop
the publication as well to drop the triggers which will slow the delete), do
the delete, and then recreate the publication and subscription and do a no
sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi Hilary,
> Thanks for your response, this is not going to be possible as we use the
> ActiveX control and the publications have been hard coded. Which i know is
> not good. Is there any other way that we could perhaps specify the order
> that things get run in I.E sp_AddScriptexec. I have worked a way out with
> the system tables that we could remove generations. However i would need
to
> make sure that sp_addscriptexec was fired first in the replication
process.[vbcol=seagreen]
> Thanks, Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
is
>
|||Hi Hilary,
Thanks for that. What would happen to the information that was in the table
on the subscriber? Would that get deleted locally, as this information
would no longer be needed.
Again Thanks for all you help.
Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
>I think your best option would be to drop the subscription (possibly drop
> the publication as well to drop the triggers which will slow the delete),
> do
> the delete, and then recreate the publication and subscription and do a no
> sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> to
> process.
> is
>
|||It would remain there. This is probably not what you want, so you would have
to delete on both sides.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:OBtaphsrFHA.240@.tk2msftngp13.phx.gbl...
> Hi Hilary,
> Thanks for that. What would happen to the information that was in the
table[vbcol=seagreen]
> on the subscriber? Would that get deleted locally, as this information
> would no longer be needed.
> Again Thanks for all you help.
> Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
delete),[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
order[vbcol=seagreen]
with[vbcol=seagreen]
need[vbcol=seagreen]
are[vbcol=seagreen]
for[vbcol=seagreen]
a[vbcol=seagreen]
SQL[vbcol=seagreen]
Is[vbcol=seagreen]
that[vbcol=seagreen]
for
>

Deleting Database with replication

Roy,
I have no idea what 'hand picture' is, but to disable
replication on the database you can use sp_dboption or
databasepropertyex. To use sp_dboption, pass the database
name as the parameter.
published
merge publish
and set the above parameters to false.
Rgds,
Paul Ibison, SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Other than what Paul said you can also go to tools, replication, configure
publishers, distributor, and subcribers; and click on the publication
databases tab, and then uncheck the databases you want to stop
sharing/publishing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

sql

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

Deleting Conflict tables

Hi,
While trying Merge replication for various publications, many conflict
tables were created in the publisher database. Even after deleting the
publications, they haven't gone off and neither can I delete them.
Could someone suggest, how to delete the conflict tables?
Thanks!
PS.
Sometimes the sp_removedbreplication will not remove them. If you ALTER the
tables, then you should be able to just drop them.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Thanks for the response. I tried deleting them through Enterprise
Manager and I got errors, but for some reason, I am able to drop the
conflict tables by dropping them from query analyzer.
PS.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<g3Y0gn6UEHA.1996@.cpmsftngxa10.phx.gbl>...
> Sometimes the sp_removedbreplication will not remove them. If you ALTER the
> tables, then you should be able to just drop them.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
sql

Deleting a Pubisher Database

Dear All,
I recently set up a DB for replication testing. My testing
is complete so I deleted the 'Publisher' stuff in the test
database, and managed to delete the subscriber.
I then went to delete the publisher db and got the
following error message Error 3724: Cannot drop the
database 'Test' because it is being used in replication',
well it isn't.
Can anyone tell me what to do ?
Thanks
Jim
go to tools, replication, configure publishers, subscribers, and
distributor, click on the published databases tab, and uncheck any check
marks for transactional/snapshot or merge which correspond to the database
you are trying to drop.
Then try to drop your database again.
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:213b01c49f15$d0c09c70$a501280a@.phx.gbl...
> Dear All,
> I recently set up a DB for replication testing. My testing
> is complete so I deleted the 'Publisher' stuff in the test
> database, and managed to delete the subscriber.
> I then went to delete the publisher db and got the
> following error message Error 3724: Cannot drop the
> database 'Test' because it is being used in replication',
> well it isn't.
> Can anyone tell me what to do ?
> Thanks
> Jim
|||Thanks Hilary.
I think the term 'I'm not worthy, I'm not worthy' is
appropiate here. ;)
Jim

>--Original Message--
>go to tools, replication, configure publishers,
subscribers, and
>distributor, click on the published databases tab, and
uncheck any check
>marks for transactional/snapshot or merge which
correspond to the database
>you are trying to drop.
>Then try to drop your database again.
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:213b01c49f15$d0c09c70$a501280a@.phx.gbl...
testing[vbcol=seagreen]
test[vbcol=seagreen]
replication',
>
>.
>

Deletes/Inserts in Merge replication

Hi,
I am using mergereplication for doing .1 miliion records /day. I find the
replication is getting stuck because of a few deletes/updates.
Is there a better way to handle this?
Ravi,
when you mention that it is getting stuck, what exactly is the error
message - please can you post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Deletes through Replication

I have an order header we'll call HEADER and detail we'll call DETAIL
(catchy names !!)
When I replicate new orders, I use filters:
The HEADER article filter is like:
Select * from HEADER
where ordr_dte >= '1998-01-01'
then
The DETAIL filter is like:
Select * from DETAIL
where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num = HEADER.ord_num
and (HEADER.ordr_dte >= '1998-01-01' ))
This makes sure I only get DETAILS that match filtered HEADERS.
Everything's fine so far. Subscriber matches Publisher
Now, our order processing application sometimes deletes order records. Not
very often, but if a transaction is backed out before being posted or
something.
Anyway, the problem is that on the Publisher, the application deletes the
HEADER record, which gets deleted on the Subscriber .... FINE
Then the DETAIL gets deleted on the Publisher .... BUT ... since my
filter only deals with DETAILS that have an associated HEADER, and the HEADER
doesn't exist any more, I'm left with an orphan DETAIL record on the
subscriber.
What should I do ? Is there a better way to set this up to start with ?
if you are using merge replication have a look at join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> I have an order header we'll call HEADER and detail we'll call DETAIL
> (catchy names !!)
> When I replicate new orders, I use filters:
> The HEADER article filter is like:
> Select * from HEADER
> where ordr_dte >= '1998-01-01'
> then
> The DETAIL filter is like:
> Select * from DETAIL
> where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num =
HEADER.ord_num
> and (HEADER.ordr_dte >= '1998-01-01' ))
> This makes sure I only get DETAILS that match filtered HEADERS.
> Everything's fine so far. Subscriber matches Publisher
> Now, our order processing application sometimes deletes order records. Not
> very often, but if a transaction is backed out before being posted or
> something.
> Anyway, the problem is that on the Publisher, the application deletes the
> HEADER record, which gets deleted on the Subscriber .... FINE
> Then the DETAIL gets deleted on the Publisher .... BUT ... since my
> filter only deals with DETAILS that have an associated HEADER, and the
HEADER
> doesn't exist any more, I'm left with an orphan DETAIL record on the
> subscriber.
> What should I do ? Is there a better way to set this up to start with ?
|||I'm just using regular Transactional Replication.
Is there some better filter logic I can use to avoid orphans ?
"Hilary Cotter" wrote:

> if you are using merge replication have a look at join filters.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
> news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> HEADER.ord_num
> HEADER
>
>
|||Yes, but could you post your schema for both tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:582163AF-EEC9-4633-B723-70D2B72C9E1A@.microsoft.com...[vbcol=seagreen]
> I'm just using regular Transactional Replication.
> Is there some better filter logic I can use to avoid orphans ?
> "Hilary Cotter" wrote:
Not[vbcol=seagreen]
the[vbcol=seagreen]
my[vbcol=seagreen]
with ?[vbcol=seagreen]
|||Here are the schemas for the "HEADER" and "DETAIL" tables. (1 Header to
Many Details)
Below them are the snippets from my Replication Row Filters. These tables
are part of a package, so I can't make the Detail get deleted before the
Header.
Schema for PROORD_M ("HEADER" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROORD_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[CTG_DTE] [datetime] NOT NULL ,
[ORD_CTG] [char] (6) NOT NULL ,
[NUM_OPS] [char] (8) NOT NULL ,
[CTM_NBR] [char] (12) NOT NULL ,
[CTG_DTE2] [decimal](8, 0) NOT NULL ,
[ORD_CTG2] [char] (6) NOT NULL ,
[ORD_STA] [char] (1) NOT NULL ,
[ORD_NUM2] [char] (8) NOT NULL ,
[PO_NUM] [char] (20) NULL ,
[ORDR_DTE] [datetime] NULL ,
[SHP_DTE] [datetime] NULL ,
[ORD_TYPE] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROORD_M] ADD
CONSTRAINT [PROORD_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K2] UNIQUE NONCLUSTERED
([CTG_DTE],
[ORD_CTG],
[NUM_OPS]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K4] UNIQUE NONCLUSTERED
([ORD_STA],
[ORD_NUM2]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
Schema for PROOLN_M ("DETAIL" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROOLN_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[ORD_SPSQ] [char] (6) NOT NULL ,
[ORD_SEQ] [char] (5) NOT NULL ,
[SHP_CTM] [char] (12) NOT NULL ,
[ITM_NUM2] [char] (10) NOT NULL ,
[ITM_NUM3] [char] (10) NOT NULL ,
[ORD_NUM3] [char] (8) NOT NULL ,
[OLN_STA] [char] (2) NULL ,
[ITM_NUM] [char] (10) NULL
[QTY_ORD] [int] NULL ,
[QTY_SHP] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROOLN_M] ADD
CONSTRAINT [PROOLN_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM],
[ORD_SPSQ],
[ORD_SEQ]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++
Row filter for PROORD ("HEADER" Table)
SELECT <published_columns> FROM <<PROORD_M>>
WHERE ordr_dte >= '1998-01-01' or ordr_dte is null
++++++++++++++++++++++++++++
Row filter for PROOLN ("DETAIL" Table)
SELECT <published_columns> FROM <<PROOLN_M>>
WHERE exists
(SELECT * FROM AdvDbPrd.dbo.proord_m WHERE
prooln_m.ord_num = proord_m.ord_num
and (ordr_dte >= '1998-01-01' or ordr_dte is null))

Deletes on subscriber in merge replication

Using Merge replication between SQL Server 2000 and SQL Server CE,
is there any way that row deletes could occur on the subscriber without a reinitialize
or explicit delete of row on publisher.

More specifically, if there is a row filter that returns a days worth of data with each
days pull, for example, and the filter looked like
select <columns> from Table where UpdateDate < GETDATE() and UpdateDate >= DATEADD(d,1,GETDATE())
would there be some implicit delete at subscriber each day because data sent changed?

My research indicates this does not happen, but I have a colleague who thinks differently.

Merge replication is trigger-based, meaning a change has to happen for the filter to be evaluated. So basically your filter will not guarantee your subscriber to have rows that meet your filter criteria without some process that does some sort of dummy update to the rows for it to be processed.

However please look at this best practice topic "Best Practices for Time-Based Row Filters": http://msdn2.microsoft.com/en-us/library/ms365153.aspx, it shows how you can accomplish what you want.

|||

Thanks for the clarification. I have been able to convince myself that deletes occur on the subscriber for records outside the filter if I apply some update. I am trying to help a customer with replication performance issues. The application does a reinitialize on every request from the user for synchronization. This is sending 10000 or more rows. When I asked them why they do a reinit every time, they were not sure (the original programmer is gone), but they said there was a problem with deletes taking so long and it was better to just reinit. That is why I asked the original question. I did not know what deletes they could be talking about.

I am curious about the deleted records. It seems that the server must be sending over deletes for every updated record outside the filter. In my case, the filter is not only time based but uses HOST_NAME(). There are about 1000 handhelds out there. So if I used the method of the best-practices article, it would seem that with each synch I would be sending over deletes for all 1000 users each time.

Maybe that was what the original programmer did and what was taking too much time (even though there would be no actual delete, because those other records were never there).

Is this the behavior that would occur?

Thanks again

Ed Santosusso

|||

In SQL 2000, yes deletes are much slower compared to inserts and updates, but this has been improved in SQL 2005 provide you use the new pre-computed partitions.

|||

Moving data in and out of a partition can send over deletes, it's the only way to remove the data from the subscriber. And in SQL 2000, yes deletes are much slower compared to inserts and updates, but this has been improved in SQL 2005 provide you use the new pre-computed partitions.

Deletes occurring at publisher

I'm using Merge replication on a table with an autoidentity field. All
users are entering data at the publisher (no records are ever deleted),
and at the subscribers the data is read-only. I'm having a problem
with records being deleted at the publisher. I'm assuming it has to do
with a subscriber having problems, but I can't track it down.
Moving forward (SQL 2005), I want to change the replication type to
transactional. Could someone please tell me the best type of
replication to use in order to prevent records from being deleted at
the publisher?
Any help would be greatly appreciated.
Thanks,
Amy Marshall
Does anything show up in the conflict viewer? The deletes could be logged
here. Are you using join filters? These can cause these types of deletes,
but normally not on the publisher.
Transactional replication is designed for one way replication. It is not
clear to me what your data flow requirements are. With transactional
replication users will be able to delete data in any location, but only
deletes occurring on the publisher will be replicated to the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<marshallae@.bowater.com> wrote in message
news:1138635297.513324.39100@.g47g2000cwa.googlegro ups.com...
> I'm using Merge replication on a table with an autoidentity field. All
> users are entering data at the publisher (no records are ever deleted),
> and at the subscribers the data is read-only. I'm having a problem
> with records being deleted at the publisher. I'm assuming it has to do
> with a subscriber having problems, but I can't track it down.
> Moving forward (SQL 2005), I want to change the replication type to
> transactional. Could someone please tell me the best type of
> replication to use in order to prevent records from being deleted at
> the publisher?
> Any help would be greatly appreciated.
> Thanks,
> Amy Marshall
>
|||Amy,
the issue might be caused by compensating changes. Please take a look at :
http://support.microsoft.com/default...&Product=sql2k
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This morning I had 264 conflicts, which I kept to make sure I got the
deleted records back into the table, but this was the first time there
were any conflicts at all for that table. We are not using any join
filters on this table. I was thinking of using transactional
replication, because all entries into the problem table are done at the
publisher, and the inputs into that table are synched to the
subscribers based on a timeframe that is set from an application which
determines sync times. (Most users set synch times anywhere from 60
minutes - 240 minutes).
What's weird, is that there is a table that is in the same merge
package, that links to an ID in the problem table, but those records
are NEVER deleted at the publisher (no foreign key constraint exists
between the 2 tables). This one table seems to have the problem, and
while monitoring replication, I rarely ever see problems with
subscribers and this package.
Thanks for your input.
Amy

Deletes in the subscriber

Hi,
I'm having a problem with replication.
The situation is the following:
Operating System of the server: Win server 2003
Database: SQL 2000
Subscribers: SQLCE in PDA's HP5550
Number of subscribers: 23
Subscription: Daily
Somme of the subscribers (PDA) take some a long time to process all the information from the replications. This process of replication take 30 to 45 minutes. In the replication monitor i see that it sends something like 230 000 deletes. It′s a lot for a
PDA processing.
What can i do to improve this situation.
Thatk's for your atention
Best Regards
PPereira
Hi PPereira,
You might be interested in the following document:
Diagnosing and Troubleshooting Slow Partitioned Merge Processes
http://msdn.microsoft.com/library/de...artitioned.asp
Hope this helps,
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
PPereira wrote:
> Hi,
> I'm having a problem with replication.
> The situation is the following:
> Operating System of the server: Win server 2003
> Database: SQL 2000
> Subscribers: SQLCE in PDA's HP5550
> Number of subscribers: 23
> Subscription: Daily
> Somme of the subscribers (PDA) take some a long time to process all the information from the replications. This process of replication take 30 to 45 minutes. In the replication monitor i see that it sends something like 230 000 deletes. It′s a lot for
a PDA processing.
> What can i do to improve this situation.
> Thatk's for your atention
> Best Regards
> PPereira

Deleted record

Suppose I delete a record in a database at 12pm.
And one staff modified the same record in another database at 1 pm.
Aftere the merge replication, does that record still in both databases or
not.
By default a row deleted at the publisher will be replicated to all
subscribers as a delete. The updated row on the subscriber would be logged
as a delete. A delete originating at all subscribers would be logged as a
conflict and be replaced with an update flowing from the subscriber.
If a row was deleted at a subscriber and then updated at another subscriber
the first one in would remain, and the later one would be rolled back and
logged in the conflict table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23oN$CLqKGHA.140@.TK2MSFTNGP12.phx.gbl...
> Suppose I delete a record in a database at 12pm.
> And one staff modified the same record in another database at 1 pm.
> Aftere the merge replication, does that record still in both databases or
> not.
>
|||Not really.
If you had the following:
UserA deletes row at noon on Server1 and UserB updates that row on Server2
at 1PM, if the merge engine does not synchronize between the delete and the
update, then you have created a conflict that I refer to "update of a
non-existent row". In this case, if your conflict resolution states that
Server1 should win, then the delete will go through to both servers and the
update will be logged in the conflict table at the publisher. If your
conflict resolution states that Server2 should win, then the update is
applied to both servers and the delete is logged to the conflict table at
the publisher.
However, if UserA deletes row at noon on Server1 and UserB updates that row
on Server2 at 1PM, and the merge engine performed a synchronization cycle
between the delete and the update, then nothing would happen. The update
would change zero rows since the row would not exist in the database. Since
no rows had changed, the merge update trigger would simply return without
logging anything into MSmerge_contents.
The piece of the merge trigger that governs this behavior is as follows:
declare @.article_rows_updated int
select @.article_rows_updated = count(*) from inserted
if @.article_rows_updated=0
return
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eCpAC8sKGHA.2064@.TK2MSFTNGP11.phx.gbl...
> By default a row deleted at the publisher will be replicated to all
> subscribers as a delete. The updated row on the subscriber would be logged
> as a delete. A delete originating at all subscribers would be logged as a
> conflict and be replaced with an update flowing from the subscriber.
> If a row was deleted at a subscriber and then updated at another
> subscriber the first one in would remain, and the later one would be
> rolled back and logged in the conflict table.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23oN$CLqKGHA.140@.TK2MSFTNGP12.phx.gbl...
>

deleted many records, want to stop that replication

I deleted tons of recors in one table on the publisher. I want to stop
those from replicating (for the time being or forever - doesn't matter)
since it is sucking all of the replication time.
Is there a way to do that, if so, how?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
More specifics would be helpful for us to answer the question. You said you
want to stop "those" from replicating. What are "those"? The more
information you send (within reason! "It all started when I was a
child..."), the better chances are that someone can answer.
Thanks,
Mike
"Darin" <darin_nospam@.nospamever> wrote in message
news:%23fL8GalNFHA.576@.TK2MSFTNGP15.phx.gbl...
> I deleted tons of recors in one table on the publisher. I want to stop
> those from replicating (for the time being or forever - doesn't matter)
> since it is sucking all of the replication time.
> Is there a way to do that, if so, how?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***

Deleted 3.3 million records, and now replication hung

I deleted 3.3 million records out of a table on the distributor. They
merged correctly to one subscriber but the other two aren't getting the
deletes. Since the only deletions on that table would be the ones I did,
can I run the following with no adverse effects:
delete from msmerge_tombstone
where tablenick in (select nickname from sysmergearticles where
tablenick=nickname and name='MyTable') and
rowguid not in (select aud_rowguid from MyTable where
aud_rowguid=rowguid)
Darin
*** Sent via Developersdex http://www.codecomments.com ***
While you could do this it is not advisable as you will run into problems
later on down the road. I would drop the subscribers, do the delete and then
redeploy the subscriber(s).
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darin" <darin_nospam@.nospamever> wrote in message
news:%232dmt9pLIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I deleted 3.3 million records out of a table on the distributor. They
> merged correctly to one subscriber but the other two aren't getting the
> deletes. Since the only deletions on that table would be the ones I did,
> can I run the following with no adverse effects:
> delete from msmerge_tombstone
> where tablenick in (select nickname from sysmergearticles where
> tablenick=nickname and name='MyTable') and
> rowguid not in (select aud_rowguid from MyTable where
> aud_rowguid=rowguid)
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***