October 28, 2008 at 11:57 am
Hi,
We are replicating from SQL2005 to SQL2005. Distributor is SQL2005 too, separate box.
There are no errors in replication. We remove a publication from replication on a daily bases, because we need to truncate. We add back those tables into replication, of course. These are passive tables, they do not get data while we remove and add them back. However, while that is happening, other subscriptions are "loosing" transactions. 60 rows, 100, rows, from time to time. Why is this happening?
Anybody ever had this eperience?
Cheers,
Janos
October 28, 2008 at 1:46 pm
October 28, 2008 at 3:20 pm
I was thinking of maybe I am switching off the entire log reader, But i don't.
I hope this helps.
exec sp_dropsubscription
@publication = N'mypublication'
, @article = N'all'
, @subscriber = @subscriber1
if @subscriber1 <> @subscriber2
begin
-- drop the 2nd Subscriber's subscription if it exists
exec sp_dropsubscription
@publication = N'mypublication'
, @article = N'all'
, @subscriber = @subscriber2
end
-- Drop ALL articles from the publication
exec sp_droparticle
@publication = N'mypublication'
, @article = N'all'
, @force_invalidate_snapshot = 1
-- truncate tables
truncate table dbo.Table1
truncate table dbo.Table2
truncate table dbo.Table3
truncate table dbo.Table4
truncate table dbo.Table5
truncate table dbo.Table6
truncate table dbo.Table7
truncate table dbo.Table8
exec sp_addarticle
@publication = N'mypublication'
, @article = N'Table1'
, @source_owner = N'dbo'
, @source_object = N'Table1'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'none'
, @schema_option = 0x000000000803509F -- 0x0000000000004071
, @force_invalidate_snapshot = 1
, @identityrangemanagementoption = N'none'
, @destination_table = N'Table1'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable1' -- SQL
, @del_cmd = N'NONE' -- NONE
, @upd_cmd = N'SCALL sp_MSupd_dboTable1' -- SQL
exec sp_addarticle
@publication = N'mypublication'
, @article = N'Table2'
, @source_owner = N'dbo'
, @source_object = N'Table2'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'none'
, @schema_option = 0x000000000803509F -- 0x0000000000004071
, @force_invalidate_snapshot = 1
, @identityrangemanagementoption = N'none'
, @destination_table = N'Table2'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable2' -- SQL
, @del_cmd = N'NONE' -- NONE
, @upd_cmd = N'SCALL sp_MSupd_dboTable2' -- SQL
exec sp_addarticle
@publication = N'mypublication'
, @article = N'Table3'
, @source_owner = N'dbo'
, @source_object = N'Table3'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'none'
, @schema_option = 0x000000000803509F -- 0x0000000000004071
, @force_invalidate_snapshot = 1
, @identityrangemanagementoption = N'none'
, @destination_table = N'Table3'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable3' -- SQL
, @del_cmd = N'NONE' -- NONE
, @upd_cmd = N'SCALL sp_MSupd_dboTable3' -- SQL
exec sp_addarticle
@publication = N'mypublication'
, @article = N'Table4'
, @source_owner = N'dbo'
, @source_object = N'Table4'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'none'
, @schema_option = 0x000000000803509F -- 0x0000000000004071
, @force_invalidate_snapshot = 1
, @identityrangemanagementoption = N'none'
, @destination_table = N'Table4'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable4' -- SQL
, @del_cmd = N'NONE' -- NONE
, @upd_cmd = N'SCALL sp_MSupd_dboTable4' -- SQL
exec sp_addarticle
@publication = N'mypublication'
, @article = N'Table5'
, @source_owner = N'dbo'
, @source_object = N'Table5'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'none'
, @schema_option = 0x000000000803509F -- 0x0000000000004071
, @force_invalidate_snapshot = 1
, @identityrangemanagementoption = N'none'
, @destination_table = N'Table5'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable5' -- SQL
, @del_cmd = N'NONE' -- NONE
, @upd_cmd = N'SCALL sp_MSupd_dboTable5' -- SQL
exec sp_addarticle
@publication = N'mypublication'
, @article = N'Table6'
, @source_owner = N'dbo'
, @source_object = N'Table6'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'none'
, @schema_option = 0x000000000803509F -- 0x0000000000004071
, @force_invalidate_snapshot = 1
, @identityrangemanagementoption = N'none'
, @destination_table = N'Table6'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable6' -- SQL
, @del_cmd = N'NONE' -- NONE
, @upd_cmd = N'SCALL sp_MSupd_dboTable6' -- SQL
exec sp_addarticle
@publication = N'mypublication'
, @article = N'Table7'
, @source_owner = N'dbo'
, @source_object = N'Table7'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'none'
, @schema_option = 0x000000000803509F -- 0x0000000000004071
, @force_invalidate_snapshot = 1
, @identityrangemanagementoption = N'none'
, @destination_table = N'Table7'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable7' -- SQL
, @del_cmd = N'NONE' -- NONE
, @upd_cmd = N'SCALL sp_MSupd_dboTable7' -- SQL
exec sp_addarticle
@publication = N'mypublication'
, @article = N'Table8'
, @source_owner = N'dbo'
, @source_object = N'Table8'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'none'
, @schema_option = 0x000000000803509F -- 0x0000000000004071
, @force_invalidate_snapshot = 1
, @identityrangemanagementoption = N'none'
, @destination_table = N'Table8'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable8' -- SQL
, @del_cmd = N'NONE' -- NONE
, @upd_cmd = N'SCALL sp_MSupd_dboTable8' -- SQL
-- add back the subscription for the 1st Subscriber
exec sp_addsubscription
@publication = N'mypublication'
, @subscriber = @subscriber1
, @destination_db = @db1
, @sync_type = N'replication support only'
, @subscription_type = N'push'
, @update_mode = N'read only'
exec sp_addpushsubscription_agent
@publication = N'mypublication'
, @subscriber = @subscriber1
, @subscriber_db = @db1
, @subscriber_login = 'login'
, @subscriber_password = 'password'
, @subscriber_security_mode = 0
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20080709
, @active_end_date = 99991231
, @enabled_for_syncmgr = N'False'
, @dts_package_location = N'Distributor'
October 28, 2008 at 3:26 pm
October 28, 2008 at 3:30 pm
Deletes are expensive for us. The combined rowcount of the 8 tables can reach 2 million rows per day. I am not replicating deletes.
October 29, 2008 at 8:10 am
Ah, now I see that in the script you provided. Serves me right for trying to finish my response before I left for the day. :doze:
Just so I am clear, are you saying that you're missing rows at the subscriber for the tables that are part of the publication you're tinkering with, or are you saying that you're missing rows for tables that are part of other publications that you don't touch?
October 29, 2008 at 9:49 am
I am having problems with other publications. They "loose" transactions.
There is one more thing. The 8 tables I am truncating is in a view with another 8 tables. So, imagine a 16 tables view. The first set of 8 tables is for odd days, and the second set of 8 tables is for even days. I am inserting into the view through an instead of trigger where I decide to which table the actual insert goes. So there is always a set of 8 tables that can be truncated, i.e. yesterday's data. Now, while I am switching off yesterday's tables from the replication and truncating them I am loosing transactions on the subscriber side for today's tables. Most weird.
October 29, 2008 at 9:59 am
Do you mean you have two sets of tables and you are replicating both to a single table on the subscriber?
If you're turning replication on and off, you might lose transactions. If you have it running, you won't lose transactions.
If your idea is to remove data on the publisher and not have that replicate, you're doing this wrong. Use a trigger or manual process to move the data from the original table to a staging table, keep all data there, and replicate that.
October 29, 2008 at 10:33 am
Ok, t11, t12, t13, ..., t18 is collecting transactions throughout today. I am replicating them to the subscriber to t11, t12, ..., t18 tables.
T21, T22, ..., T28 are "passive tables", meaning that they hold yestardays's data. They are static. So I can go, turn off replication on them, truncate them, put back into replication. The next day, t21, t22, ..., t28 will collect transactions and t11, t12, ..., t18 will be "passive" so I can remove them from replication, truncate them and put them back.
The problem is that while I am truncating t11, t12, ..., t18, my ongoing subsciption for t21, t22, ..., t28 skips transactions. And vice versa.
October 29, 2008 at 11:31 am
October 29, 2008 at 11:43 am
Yes, sorry, t11, t12,..., t18 is pub1
and t21, t22, ..., t28 is pub2
They are separate.
This is the puzzling thing. While I am turning off and on pub1, pub2 looses transactions. And vice versa. Should not be the case.
I can have tons of different publications and while I am mocking with one, the others should not be affected.
Now, the only commonality between pub1 tables and pub2 tables is the view on top of all T11, ..., t18, t21,...,t28 tables with the insteadof trigger on it.
On the publisher I am not loosing transactions, it is not that inserts are stopped from happening. It is just as if the whole logreader would be turned off and on, the subscriber does not have some rows. And it is only for the duration of removing the pub and adding back.
October 31, 2008 at 9:57 am
Yes, they are separate publications.
We can reproduce the whole thing in our lab, so we might file this to Microsoft.
In the meantime I think we are going back to delete and give up the turn off, turn on replication thingy.
November 1, 2008 at 7:43 am
I recall reading something about replication in one of the latest SQL Patches - i forget which one it was but it says errors with replication.
Have a look at the Patches it does mentions errors with replications.
We are on CU 6 and occasionaly it errors where the log read waits a bit then catches up but usually when under heavy load.
November 4, 2008 at 3:32 pm
I checked what you said, and we are upped to build 3161 even though it did not address the problem exactly we have. And it did not help.
But, amazingly, if we change the sync type from 'replication support only' to 'none', the problem is gone! The tests are very promising so far.
November 4, 2008 at 4:11 pm
where do you put sync type ? just for further reference.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply