December 15, 2004 at 11:13 am
So, I'm running a few windows 2000 servers that all have sql server 2000 on them. Our main server merge replicates with the subscribers.
Anyway, just pushed out a new database recently, and I've been having lots of trouble with it. For some reason, the identity ranges aren't seeding properly (yes, everything's set with the NOT FOR REPLICATION option, and I'm using ranges of 100,000).
using DBCC CHECKIDENT, I get the current identity value vs. the current column value in the table. Here's an example of a table that's off:
the table is Brokers. The current identity value is 1894. I look in the table, and the next identity value is free. The constraint on the table says the identity can be anywhere between 1888 and 200,000. This would normally be fine, but there are values in that range at around 70,000. So, what would happen over time is that the identities would normally get used up, but when the next value is 70,000, I'd get a primary key error.
The example is something that's happening over a handful of our tables. Sure, I can work around it by reseeding the value after the error occurs, using sp_adjustpublisheridentityrange, and it's fixed. But, I'm perplexed as to why this is happening. I've had merge replication errors before that resulted in a call to Microsoft, which left them scratching their heads and saying it'd probably be fixed in Yukon, but nothing on this scale.
In a desparate workaround, I dropped the subscribers and the publisher last night and recreated replication. Afterwards, I pushed out the subscribers again, and I'm still getting the primary key violation error (at both the publisher site and the subscribers). The reseeding fixes the issue, but can anyone offer an explaination as to why this kind of problem might be occurring?
Yes, my sql boxes are fully patched, running the latest updates on windows as well as the latest sql server patches.
December 15, 2004 at 12:10 pm
oh, also, here's something I just fixed. In one of our subscriber sites, a table has a range of 300,001 - 400,000. For some reason, the next identity value was at 300,022 when 300,001-300,492 were taken up. I'm not sure why the identity value got set at a value that's already used...
December 16, 2004 at 12:25 am
I've experienced the same problem in the past. The quickest way to get yourself setup is to create a ReseeIdentities sproc with code like:
DECLARE @currentMax int
select @CurrentMax = max(identityCol) +1 from myTable
DBCC CHECKIDENT ('myTable',RESEED,@CurrentMax)Run that AFTER you have synced your replication, but before you start allowing transactions from your application.
Julian Kuiters
juliankuiters.id.au
December 16, 2004 at 9:35 am
Thanks for the advice, but that probably won't work very well if I'm using identity ranges. If my publisher database is using 1-1000 and is on 115, and my subscriber database is using 1001-2000 and is on identity value 1020, the script would automagically update the identity value on the publisher to 1021, which would cause a conflict with the values in the subscriber.
December 16, 2004 at 10:47 pm
Good point there.
I 'step' the identity across multiple servers, so if I have 5 servers in the replication, I set the first server to IDENTITY(1,5) the second to IDENTITY(2,5) and so on, that way they won't collide. The above code I wrote then also uses + (1 - max(identitycol) %servernumber)) so that each server is set to its correct seeding
If you are using identity ranges however, why not peek what the current max is over all the databases, and then start your ranges well above that? ie: if the current max max is 30,000 and you expect 10,000 rows before implementation, start your ranges from 50,000 or 60,000 or jump straight to 1,000,000.... If your identity is an int, you've got millions of numbers you can skip over without any worries.
Julian Kuiters
juliankuiters.id.au
December 17, 2004 at 9:23 am
well, I currently have the ranges set at 100,000 in all the areas, which should be more than adequate. But, when your existing data has values in the 75,000 range, replication shouldn't be starting your identity seed on your publisher at 1500. There's a problem with merge replication, and I'm scheduled to talk to Microsoft this morning about it.
But yeah, I never thought of stepping my identity values like that. A problem that occurred to me though is the fact that if you add a new server to the scheme, it'll have you manually adjusting the identities on your servers. But, thanks for your thoughts, there's just something about merge replication that needs to be fixed.
December 30, 2004 at 3:07 pm
Microsoft just told me that the problem will be fixed in SP4, which will be released Q1 of 2005. It'll also be fixed when Yukon comes out.
January 4, 2005 at 11:57 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply