 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Replication / April 2006Subscriber Identity Range Mgmt Problem (Merge Repl)
I have just implemented merge replication with one subscriber. I have a "SLI" table that has an identity column in it. When I set up replication, I configured the identity range management as follows: Range size at Publisher: 10000 Range size at Subscriber: 10000 Assign a new range %...: 80 On the publisher, prior to implementing replication, my max identity value in the SLI table was 33286. Upon implementing replication, there were no SLI records on the subscriber. Once the subscriber users began adding records to the SLI table, the identity value started at 90001 and incremented properly to 90920. The next identity value on the subscriber then jumps to 505989825. Then, the very next identity value jumps to 614293248! What the?!? I've searched the internet and have found nothing on subscriber identity values soaring through the roof like this. Also, this is the second time I've submitted a question here on this issue, but I've not received any additional information other than what's widely published (and that I've already consumed multiple times). Is nobody else in the SQL/Replication community experiencing identity values jumping through the roof on their subscribers? It's hard to imagine that I'm the only one with this issue. Could this be caused by the application somehow? It doesn't seem so, because replication is managing these identity columns/values. I'm completely stumped by this, but it will become a serious issue if the identity values max out while a subscriber is trying to add records. Any help will be greatly appreciated. Thanks! Jeff Jones Atlanta, GA Can you do the following for me: on the publisher issue the following in the publication database dbcc checkident('SLI') select * from MSrepl_identity_range on the distributor issue the following select * from MSrepl_identity_range and on the subscriber issue the following select * from MSrepl_identity_range dbcc checkident('SLI') and then identify your replication check constraint, it will look something like this: repl_identity_range_PublicationName_GuidCorrespondingToTheEntryForTheTableInSysMergeArticles and do the following sp_help repl_identity_range_PublicationName_GuidCorrespondingToTheEntryForTheTableInSysMergeArticles sp_helptext repl_identity_range_PublicationName_GuidCorrespondingToTheEntryForTheTableInSysMergeArticles Are any other publications referencing this table?  SignatureHilary 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 >I have just implemented merge replication with one subscriber. I have a > "SLI" table that has an identity column in it. When I set up replication, [quoted text clipped - 38 lines] > Jeff Jones > Atlanta, GA Hilary, See comments below... FYI: the subscription db was created from scratch on the subscriber, prior to initialization. Jeff > Can you do the following for me: > > on the publisher issue the following in the publication database > > dbcc checkident('SLI') Checking identity information: current identity value '33289', current column value '614293248'. > select * from MSrepl_identity_range objid next_seed pub_range range max_identity threshold current_max 271496196 300 100 100 2147483647 80 300 286116260 40000 10000 10000 2147483647 80 40000 367496538 300 100 100 2147483647 80 300 423164753 220000 20000 20000 2147483647 80 220000 495496994 200 100 100 2147483647 80 200 505989825 50000 10000 10000 2147483647 80 50000 542117172 45000 5000 5000 2147483647 80 45000 543497165 5000 2500 2500 2147483647 80 5000 590117343 38000 1000 1000 2147483647 80 38000 591497336 1000 500 500 2147483647 80 1000 639497507 6000 3000 3000 2147483647 80 6000 654117571 30000 10000 10000 2147483647 80 30000 687497678 200 100 100 2147483647 80 200 734117856 150000 3000 3000 2147483647 80 150000 767497963 200 100 100 2147483647 80 200 798118084 150000 5000 5000 2147483647 80 150000 814834165 11000 1000 1000 2147483647 80 11000 815498134 700 100 100 2147483647 80 700 847498248 28500 500 500 2147483647 80 28500 862118312 155000 5000 5000 2147483647 80 155000 895498419 1000 500 500 2147483647 80 1000 959498647 1000 500 500 2147483647 80 1000 1238516437 150000 1000 1000 2147483647 80 150000 1422120307 158000 2000 2000 2147483647 80 158000 1423500300 1500 250 250 2147483647 80 1500 1439500357 1500 500 500 2147483647 80 1500 1486120535 146000 2000 2000 2147483647 80 146000 1583500870 12000 3000 3000 2147483647 80 12000 1625993815 55000 1000 1000 2147483647 80 55000 1647501098 88000 500 500 2147483647 80 88000 2146978875 13000 500 500 2147483647 80 13000 > on the distributor issue the following > > select * from MSrepl_identity_range Note: "SLI" tablename = "SettlementLineItem" publisher publisher_db tablename identity_support next_seed pub_range range max_identity threshold current_max RMPPLUS2SQL01 ResiDD SettlementLineItem 1 100000 10000 10000 2147483647 80 49999 > and on the subscriber issue the following > > select * from MSrepl_identity_range objid next_seed pub_range range max_identity threshold current_max 103671417 600 NULL 100 700 80 699 384720423 600 NULL 100 700 80 699 418816554 154000 NULL 1000 155000 80 154999 546100986 3000 NULL 500 3500 80 3499 614293248 90000 NULL 10000 100000 80 99999 818817979 154000 NULL 2000 156000 80 155999 886294217 80000 NULL 10000 90000 80 89999 949578421 2500 NULL 250 2750 80 2749 962818492 166000 NULL 2000 168000 80 167999 1030294730 70000 NULL 10000 80000 80 79999 1106819005 300000 NULL 20000 320000 80 319999 1186103266 3000 NULL 500 3500 80 3499 1190295300 175000 NULL 5000 180000 80 179999 1193771310 700 NULL 100 800 80 799 1250819518 3500 NULL 500 4000 80 3999 1316199739 18000 NULL 3000 21000 80 20999 1334295813 170000 NULL 5000 175000 80 174999 1399676034 600 NULL 100 700 80 699 1421248118 30500 NULL 500 31000 80 30999 1467152272 1100 NULL 100 1200 80 1199 1474820316 90000 NULL 500 90500 80 90499 1478296326 42000 NULL 1000 43000 80 42999 1481772336 59000 NULL 1000 60000 80 59999 1618820829 15000 NULL 500 15500 80 15499 1622296839 162000 NULL 3000 165000 80 164999 1716201164 3000 NULL 500 3500 80 3499 1833773590 24000 NULL 3000 27000 80 26999 1860201677 15000 NULL 2500 17500 80 17499 1977774103 65000 NULL 5000 70000 80 69999 2077250455 700 NULL 100 800 80 799 2086298492 15000 NULL 1000 16000 80 15999 > dbcc checkident('SLI') Checking identity information: current identity value '91213', current column value '614293248'. > and then identify your replication check constraint, it will look something > like this: [quoted text clipped - 7 lines] > sp_helptext > repl_identity_range_PublicationName_GuidCorrespondingToTheEntryForTheTableInSysMergeArticles ([SettlementLineItemID] > 90000 and [SettlementLineItemID] < 100000) > Are any other publications referencing this table? > >I have just implemented merge replication with one subscriber. I have a [quoted text clipped - 39 lines] > > Jeff Jones > > Atlanta, GA this is your problem > dbcc checkident('SLI') Checking identity information: current identity value '91213', current column value '614293248'. The last value successfully inserted as 91213, then a process inserted 614293248-91213 =614202035 rows, each of the 614202035 inserts were kicked back because of the check constraint. You need to find a value for your range which is representative of the largest batch operation or get an idea of how many inserts would happen on the publisher or subscriber between syncs and then multiply by some safety factor so that this value if greater than your range/.80. This should guarantee a new range is assigned between syncs. You have a similar problem on your publisher, only fewer inserts have happened here, so the range could be smaller. I would tear down replication, and reconfigure it, this time use larger ranges. SignatureHilary 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 > Hilary, > See comments below... [quoted text clipped - 161 lines] >> > Jeff Jones >> > Atlanta, GA Hilary, First off, thank you for spending time on this - it's been a very troubling issue indeed. Unfortunately, the SLI table receives records in one and only one way - a user enters a line item record one at a time from the application; sometimes at the publisher, but most times at the subscriber. So, there's definitely no process that could've added the 614202035 records that would've pushed the identity values up that high. At most, they may add several thousand records in a day - there's no chance that 614+ million records would've been added (either at the pub or sub). Also, there was an interim jump to 505989825, then to the 6+mil. In addition, this has happened before. So, I did exactly what you recommended - i.e., ripped out replication completely, and then reconfigured it from scratch (a painful process indeed, but the only way I thought possible to get through this issue). At that time, I performed some maintenance on the SLI table by creating a temporary SLI table with an identity column, moving all current SLI records (with astronomical identity values) into the temp table to establish new identity values that were consecutive beginning at 1. I then truncated the actual SLI table and did a reseed of it's identity column to 1 and re-inserted the records from the temp table back in. I did a dbcc checkident and all was well. This was done after replication was removed, and before it was re-established. I then created a shell database on the subscriber (i.e., no objects of any kind in it) and rebuilt replication from the ground up. Everything started great, but now all of a sudden the identity values have jumped through the roof again. I can't do that maintenance routine on a consistent basis, as we are continually adding/removing subscribers and will not have the luxury of no subscribers again anytime soon. I'm just completely stumped (and frustrated) by this issue - should I open a support case with Microsoft? Jeff > this is your problem > [quoted text clipped - 183 lines] > >> > Jeff Jones > >> > Atlanta, GA I would urge you to open a case with PSS. If you are able to repro it they should be able to fix it. I have never seen this problem before, I have a fair amount of experience with automatic identity range management, and it can work very well. My hunch is that you blew the range before a sync happened so the merge agent could adjust the range. If you know this is not true, its very puzzling. SignatureHilary 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 > Hilary, > First off, thank you for spending time on this - it's been a very [quoted text clipped - 241 lines] >> >> > Jeff Jones >> >> > Atlanta, GA Thanks Hilary - will do. I did look at the SLI table more in-depth and found a trigger that may have been being called recursively due to replication - which may well be the problem. I altered the trigger and added the "Not for replication" tag to it, which most likely will solve the problem on the SLI table. However, I do have 2 other tables (LoanException and LoanStatusHistory) that did the same thing previously, but do not have a trigger on them. So, I'm not totally convinced that the 'not for replication' tag will fix my issue. We'll see.... Thanks again for your help! Jeff > I would urge you to open a case with PSS. If you are able to repro it they > should be able to fix it. [quoted text clipped - 216 lines] > >> >> > to 90920. The next identity value on the subscriber then jumps to > >> >> > 505989825. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.