September 15, 2017 at 6:49 pm
We have 2008R2 P2P configured between 2 nodes. Dedicated distributor for both nodes.
Out of seemingly nowhere a table article starting replicating millions of rows. We ran profiler on subscriber and found many upd repl sp calls with nothing getting updated at all, see below is all default. Here is call, notice everything is default except for last 4 which are Pk, then the internal params:
exec [sp_MSupd_fxDBProductMoneyTransfer_Ops_P2P_Pub_dboOrders01322336306] default,default,default,default,default,default,default,default
,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default
,default,default,default,default,default,default,default
,53815586,0x0000000000,NULL,0x2C010000E19AB39C0A000000
sp at subscriber:
CREATE procedure [sp_MSupd_fxDBProductMoneyTransfer_Ops_P2P_Pub_dboOrders01322336306] @c1 int = NULL,
..., @c34 money = NULL, @pkc1 int = NULL, @bitmap binary(5), @MSp2pPreVersion varbinary(32) , @MSp2pPostVersion varbinary(32)
Some history - 9/13 11PM DDL deployed to table (new column) with ALTER TABLE ADD NewColumn int, this ran on Node1 only.
9/14 11.25PM pending transactions start climbing up to 10Million. We poll distributor for pending transactions every 5 minutes and saw counts starting over ~24 hours after ddl deploy (maybe related, maybe something stuck somewhere?), log reader had no issues and was moving records for all other tables in publication just fine.
Thank you in advance.
Chris B
Chris Becker bcsdata.net
September 18, 2017 at 6:22 am
When you replicate a table, and then add a new column, you'll have as many updates to replicate as there are rows in the table, as that new columns entire set of values has to be replicated. To my knowledge, the only way you stop that is to specify NOT FOR REPLICATION on a column in your DDL. So if your table has millions of rows, then there will be a LOT of replication traffic happening in order to update the replica.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 19, 2017 at 8:09 pm
We discovered root cause of issue. Due to poorly planned and tested code, a IsChanged bit column was getting updated to 1 for every row. Interestingly it was already 1 hence all the default input params.
Peer to Peer has a hidden column that will always get updated. http://replicationanswers.com/2017/01/23/peer-to-peer-the-hidden-column/
Chris Becker bcsdata.net
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply