can avoid this rollback ??

Hi,
We're testing Transactional replication with updatable subscriber. We have
kept a machine @.branch office which is the publisher and the subscriber is at
the head office. Publisher is win 2003 server with win 2000 sp4 and
subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
replication is quick but for some specific processes invloving many
transactions, the replication backlog is huge.
We used profiler and found that there is a table whci is deleted completely
during the process and populated again with current data. The table has
almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
row and then sp_MSins is run to insert data. this process takes lot of time
and creates backlog.
After the table got repopulated at subscriber and replication procs started
inserting data in another table, the publisher machine got switched off
accidently by a person at branch. When we started the machine next time, the
second tables at subscriber was empty and the profiler showed that again the
sp_MSdel was being executed at subscriber.
Does this mean that , during the process, if ne of the participating machine
is not available, the whole process will be restarted?
Why it strts with the table it had already replicated?can I stop this?
Ne suggestions?
regards,
have a look at the transaction on the publisher which caused this process.
It is probably a large update statement which is not being done as an update
in place on the subscriber, but rather being decomposed into a series of
delete and then update statements. See if you can identify if this action
was initiated by a stored procedure on the publisher and if so consider
replicating the execution of this stored procedure. You might also want to
look at using the trace flag mentioned in the following kb article which
will force an update in place.
http://support.microsoft.com/kb/302341/EN-US/
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
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
> Hi,
> We're testing Transactional replication with updatable subscriber. We have
> kept a machine @.branch office which is the publisher and the subscriber is
> at
> the head office. Publisher is win 2003 server with win 2000 sp4 and
> subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
> replication is quick but for some specific processes invloving many
> transactions, the replication backlog is huge.
> We used profiler and found that there is a table whci is deleted
> completely
> during the process and populated again with current data. The table has
> almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
> row and then sp_MSins is run to insert data. this process takes lot of
> time
> and creates backlog.
> After the table got repopulated at subscriber and replication procs
> started
> inserting data in another table, the publisher machine got switched off
> accidently by a person at branch. When we started the machine next time,
> the
> second tables at subscriber was empty and the profiler showed that again
> the
> sp_MSdel was being executed at subscriber.
> Does this mean that , during the process, if ne of the participating
> machine
> is not available, the whole process will be restarted?
> Why it strts with the table it had already replicated?can I stop this?
> Ne suggestions?
> regards,
|||Thanks for the prompt reply Hilary.
The process is actually deleting a table completely and then inserting rows
into it. This is done by a stored procedure.
Could u give me ne link to get more idea about the 'replicatin execution of
sp'.
We told the application maintenance guys this issue, anmd asked them to
change the logic to update instead of competely emptying the table.
"Hilary Cotter" wrote:

> have a look at the transaction on the publisher which caused this process.
> It is probably a large update statement which is not being done as an update
> in place on the subscriber, but rather being decomposed into a series of
> delete and then update statements. See if you can identify if this action
> was initiated by a stored procedure on the publisher and if so consider
> replicating the execution of this stored procedure. You might also want to
> look at using the trace flag mentioned in the following kb article which
> will force an update in place.
> http://support.microsoft.com/kb/302341/EN-US/
> --
> 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
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
>
>
|||Is it possible that you are doing a cascading update of the entire table or
a large portion of it?
Regarding replicating the execution of stored procedures have a look at
Publishing Stored Procedure Execution in BOL.
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
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...[vbcol=seagreen]
> Thanks for the prompt reply Hilary.
> The process is actually deleting a table completely and then inserting
> rows
> into it. This is done by a stored procedure.
> Could u give me ne link to get more idea about the 'replicatin execution
> of
> sp'.
> We told the application maintenance guys this issue, anmd asked them to
> change the logic to update instead of competely emptying the table.
> "Hilary Cotter" wrote:
|||Couldn't get u. Presently it's not update. It's a history table which
maintens one day history. So everyday, it is cleared and working table's data
is shifted ti it
I saw and tried the sp execution , works well. But in my case it won't fit,
as I have my procedure calling another procedure and BOL says that won't b
supported.
: (
"Hilary Cotter" wrote:

> Is it possible that you are doing a cascading update of the entire table or
> a large portion of it?
> Regarding replicating the execution of stored procedures have a look at
> Publishing Stored Procedure Execution in BOL.
> --
> 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
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...
>
>