Delete data in publisher database

I have set up a transactional replication to replicate data from database A to B. As the data in database A is just a temporary db, I need to clean up periodically. I have already replaced the delete command in the stored procedure so that delete action will not triggered in subscriber database when I use delete command in publisher database.

However, using delete command in publisher db will generate a huge txn log that make my drive out of disk space. I can't use 'truncate table' function as the table is participated in replication.

Anyone has a good idea how to clean up the publisher database?

thanks,
PWell...the only option I can think of will be to backup/truncate the log whenever you do batch-deletes.

BACKUP LOG MyNwind
WITH TRUNCATE_ONLY

Be aware though that when doing this the log not be recoverable! Hope this works for you...

Delete an old subscription from the subscriber?

The publisher has been disabled. But on the subscriber, I'm unable to delete
the subscription. It was a merge.
I tried to use sp_dropmergesubscription, but I've been unable to find a good
syntax example. And from which place do I run it?
Thanks in advance for any help.
Leah
If this is SQL 2000 use the following script.
http://groups.google.com/group/micro...d?dmode=source
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
"Leah" <tech@.kaplooey.com> wrote in message
news:O9LBJo2GGHA.2444@.TK2MSFTNGP11.phx.gbl...
> The publisher has been disabled. But on the subscriber, I'm unable to
> delete the subscription. It was a merge.
> I tried to use sp_dropmergesubscription, but I've been unable to find a
> good syntax example. And from which place do I run it?
>
> Thanks in advance for any help.
>
> Leah
>
|||I ran the script in both places. didn't get rifd of the subscription. In the
meantime, I'm trying to re-establish merge replication, and now I get the
cant find sp_MSupdate_replication_status in the master table. Where is this
script located, so I can reinstall it?
Thanks for your help.
Leah
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OQn9ot3GGHA.3728@.tk2msftngp13.phx.gbl...
> If this is SQL 2000 use the following script.
> http://groups.google.com/group/micro...d?dmode=source
> --
> 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
> "Leah" <tech@.kaplooey.com> wrote in message
> news:O9LBJo2GGHA.2444@.TK2MSFTNGP11.phx.gbl...
>
|||Okay - I found a source for the scripts.
http://www.webtropy.com/articles/sql...cedure.asp?SQL
I'm still trying to remove the old subscription from the subscriber.
"Leah" <tech@.kaplooey.com> wrote in message
news:eVK6y5DHGHA.984@.tk2msftngp13.phx.gbl...
>I ran the script in both places. didn't get rifd of the subscription. In
>the meantime, I'm trying to re-establish merge replication, and now I get
>the cant find sp_MSupdate_replication_status in the master table. Where is
>this script located, so I can reinstall it?
> Thanks for your help.
>
> Leah
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OQn9ot3GGHA.3728@.tk2msftngp13.phx.gbl...
>
|||http://www.replicationanswers.com/General.asp had it:
L.
"Leah" <tech@.kaplooey.com> wrote in message
news:O9LBJo2GGHA.2444@.TK2MSFTNGP11.phx.gbl...
> The publisher has been disabled. But on the subscriber, I'm unable to
> delete the subscription. It was a merge.
> I tried to use sp_dropmergesubscription, but I've been unable to find a
> good syntax example. And from which place do I run it?
>
> Thanks in advance for any help.
>
> Leah
>

delete a replicated database as publisher

Hi,
I couldn't delete a replicated db after I disabled the database as the
publisher.
any ideas?
Thanks,
You should be able to. What is the error message you are getting?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uuoQzfb4GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I couldn't delete a replicated db after I disabled the database as the
> publisher.
> any ideas?
> Thanks,
>
|||Thanks, Hilary.
I got error says that Db can't be dropped because is being replicated.
Thanks again
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4T1iBd4GHA.3404@.TK2MSFTNGP04.phx.gbl...
> You should be able to. What is the error message you are getting?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uuoQzfb4GHA.3604@.TK2MSFTNGP03.phx.gbl...
>
|||Sorry,
I re-tried to disable the publisher again. It tells me that not able to
disable the publisher beacuse distribution is in use .
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4T1iBd4GHA.3404@.TK2MSFTNGP04.phx.gbl...
> You should be able to. What is the error message you are getting?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uuoQzfb4GHA.3604@.TK2MSFTNGP03.phx.gbl...
>
|||sounds like it is messed up somehow. do the following
sp_replicationdboption 'problemdbname','publish','true'
GO
sp_replicationdboption 'problemdbname','merge publish','true'
GO
sp_replicationdboption 'problemdbname','publish','False'
GO
sp_replicationdboption 'problemdbname','merge publish','False'
GO
This should clear the condition.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23U%23gU3i4GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks, Hilary.
> I got error says that Db can't be dropped because is being replicated.
> Thanks again
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O4T1iBd4GHA.3404@.TK2MSFTNGP04.phx.gbl...
>
|||Do any other servers use this server as a distributor?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Offix%23i4GHA.2264@.TK2MSFTNGP06.phx.gbl...
> Sorry,
> I re-tried to disable the publisher again. It tells me that not able to
> disable the publisher beacuse distribution is in use .
> Thanks
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O4T1iBd4GHA.3404@.TK2MSFTNGP04.phx.gbl...
>

Delay before publishing

Hello
Is it possible that the Publisher could wait some time ( about 15 seconds)
before it starts publishing ?
I want it to start publishing when it gets new data, but there can be about
1000 new records in few seconds and I think it would be better to
wait for few seconds then to create new publication after single record is
inserted.
Best Regards
Wojciech Znaniecki
Wojciech ,
there is a difference between creating a publication and synchronizing data
for an existing publication. The publication can be created and as long as
the logreader and distribution agents don't run (no synchronization) there
is no effect on the publisher. Typically the log reader runs continuously,
but you can schedule the distribution agent to run whenever you want. If
this also runs continuously and you want to enforce a delay, you could
increase the POLLINGINTERVAL parameter's value. Other parameters you might
be interested in are -CommitBatchThreshold, CommitBatchSize,
MaxDeliveredTransactions.
HTH,
Paul Ibison
|||Thanks for fast anwser
I've tried to do it but I cant create a publication with stopped
Distribution Agent and Log Reader Agent.
Can I do it with sql script?
for example: my script that create Publication looks like that :
"(...)
exec sp_addpublication @.publication = N'RozkazyReplikacja', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of replikacyjna database from Publisher
WOJTEK-Z\W1.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran
= N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'WOJTEK-Z\W1-replikacyjna-5'
exec sp_addpublication_snapshot @.publication = N'RozkazyReplikacja',
@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval =
0, @.frequency_recurrence_factor = 1, @.frequency_subday = 4,
@.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date =
0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.snapshot_job_name = N'WOJTEK-Z\W1-replikacyjna-RozkazyReplikacja-7'
GO
(...) "
What should i change to create publication without starting Distribution and
Log Reader Agent ?
Best Regards
Wojciech Znaniecki
|||Wojtek,
using sp_addsubscriber will allow you to set the frequency for the
distribution agent. However, the easeist way to do what you require is to
create the publication without starting the snapshot agent. Then edit the
logreader and distribution agent jobs to run on a schedule.
HTH,
Paul Ibison
|||Can I set logreader and distribution agent to run more then once per minute ?
Or could I create a trigger to start, let logreader and distribution reader
to their job and stop them ?
Best Regards
Wojciech Znaniecki
"Paul Ibison" wrote:

> Wojtek,
> using sp_addsubscriber will allow you to set the frequency for the
> distribution agent. However, the easeist way to do what you require is to
> create the publication without starting the snapshot agent. Then edit the
> logreader and distribution agent jobs to run on a schedule.
> HTH,
> Paul Ibison
>
>
|||Wojciech,
you can use sp_start_job to start the agents if you want to do it
programatically. If you want to schedule it, AFAIR the maximum frequency is
1 minute or you can run continuously. If the latter, the pollinginterval
will potantially increase it in a more granular way. However, the log reader
agent normally runs continuously and the distribution agent may be
controlled in such a way. The log reader agent marks the log as having been
read, and if it is not run very frequently, the log will increase in size
and not be fully truncated during a backup.
HTH,
Paul Ibison