March 27, 2008 at 9:11 am
We are currently using Merge Replication and allowing Sql to manage the identity ranges on the Publisher and the subscriber. The problem we are having is that sometimes the reallocation doesn't take place on the publisher and we start getting constraint errors on the table that hasn't allocated when inserts are trying to take place. It seems that the trigger isn't firing that checks to see when the constraint going to be exceeded. Has anyone else had this problem? It seems that sometimes it works and sometimes it doesn't and i don't know why.
April 3, 2008 at 3:50 am
Hi,
we have exactly the same problem. I have installed SP2 over the SQL 2005, but it didn't help. My problem is that replication is done over the Internet. One server is in Bratislava (publisher) and another one in Melbourne (subscriber). The identity range is set up just for 1000 records. I am not able to change it. Or better to say, I don't know where to change it. From my point of view trigger is not running. Immediately when this number is exceeded replication stops with the following error:
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identity (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417%5B/b%5D
What snapshot format do you use? NATIV or CHARACTER? Because of this error the replicated table is locked and it is not accessible for the users.
regards
Ivan
April 14, 2008 at 11:31 am
1. using INT IDENTITY ... gives 2 ** 32 potential values (it's a lot!)
2. having long network latencies can mean that small allocations can be eaten up too quickly and you get collisions [BAD!]
what you need is large identity ranges to avoid this. You should
A. use SSMS to generate the drop (subs, arts, pub)
B. ditto for creation scripts (pub, arts, subs)
B. edit these to be readable
C. change parameters appropriate to your environment
D. issue the script to your publisher(s), which will "rip & replace" anything existing
- i.e. schedule it at quietest possible time, allowing for timezones etc
and don't forget to put all such scripts under source-code control (TFS/VSS) just like .SQL, .CS
- you DO use source-code control don't you ?!
HTH
Dick
April 17, 2008 at 12:24 pm
After working with Microsoft on this problem for the last month or so it has been determined there is a bug and currently no fix in the works since this brand new. The work around is to catch the 548 error and use the sP_Adjustpublisheridentityrange in the proc to adjust the ranges in the table when it fails
October 3, 2008 at 1:40 am
I had the same error so I traced the synchronization in Profiler. The article trying to allocate new range before the error apperance was the key to solution. My identity column was numeric(6,0) and the new range had a 7 digit number. The max range on the publisher was 999999.
Then I manually changed publisher range by making range_end parameter bigger for that particular article on the published database:
update dbo.MSmerge_identity_range
set range_end=99999999
where is_pub_range=1
and artid='041DDDB0-1CC0-4BEB-9132-698EB2E57546'
This helped me and the sync was successfull after this change.
Of course you should also make shema changes (make identity column bigger) if you plan to insert data in this table on subscribers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply