As a follow-up to yesterday's problems with exhausted identity ranges, I have come to a realization which I am now adopting as a best practice and I highly recommend for others:

The reasoning behind this advice is that when @identityrangemanagementoption = 'auto' (the default in Management Studio), identity ranges will be allocated for all subscribers, even though they will never use these ranges because the table article is read-only. Yet, if the table has a small datatype for a primary key (such as tinyint or smallint) it is quite likely that the identity range will be exhausted (with the default identity range being 1000, this doesn't take many subscribers) and when this happens synchronization will cease.

If there is a reason for allocating these ranges to read-only table articles or for leaving automatic identity range mangement enabled, please post it in the comments.

First, some background: I am using a (very) simple replication topology with a single publisher running SQL Server 2005 and ~10 subscribers running SQL Server 2005 Express (all with the latest service packs and patches, of course). No re-publishing servers, multiple publications, etc. The error occurred not long after I had added 2 new subscribers (which turns out to be quite important).

Note: For replication topologies which do include a re-publishing server, the error may be explained by KB941989, which can be solved by installing Cumulative Update 4 (or later updates including the fixes).

To find the cause of this error message, some technical information about how identity values are managed during replication is needed. A key source of information for table articles with identity columns is the MSmerge_identity_range table. This table contains a row for each range of values assigned to each subscriber for each article as well as an additional row holding the entire range of identity values for the table article (this is the row with is_pub_range = 1). In order to make sense of this table, the artid column can be joined to the sysmergearticles table and subid can be joined to the sysmergesubscriptions table. Take note of the max_used column (which only has a value for pub_range rows); this column holds the end of the maximum range that has been allocated for the article (technically 1-past-the-last, since it holds the max of next_range_end, but conceptually it is the end). When this value is near the end of the pub_range, then the available identity ranges are almost exhausted.

One quick way to determine if the problem is due to exhaustion of the available identity range, is with a query similar to the following:

Note: In the (very unusual) case that the subscribers are given larger ranges than the publisher(s), replace pub_range with sub_range in the above query. If this query returns any rows, then the problem has been identified. The table articles listed have exhausted their available identity ranges. If the range_end value is below the maximum value for the identity datatype, then it should be possible to increase this value and solve the problem quite easily (Note: I have not found a documented way to do this "safely" and modifying this table directly is probably dangerous, but it solved the problem for me. YMMV). If the range_end value is the maximum for the datatype, then the underlying datatype will likely need to be changed (or significant compaction of the values will need to be done, somehow) and the article dropped and re-added to the publication.

Other sources that I have come across suggested running the sp_adjustpublisheridentityrange stored procedure. It should be safe to run in any case, although I didn't find it particularly useful.

Another suggestion for similar errors that was suggested on the MSDN Forums is to check the identity value on the publisher and that it is within the range allocated to the publisher (both in MSmerge_identity_range and in the check constraint that replication adds to the table for checking the identity range value).

If all of the above suggestions fail, my best suggestion is to watch the synchronization in SQL Profiler (for server versions where SQL Profiler is not available, AnjLab SQLProfiler is a nice alternative) and to examine some of the replication stored procedures using Management Studio (right click->Modify to see the SQL of the SP) to get ideas for what might be going wrong. In particular, consider examining sp_adjustpublisheridentityrange, MSget_identity_range_info, MSpub_adjust_identity, and similar identity management stored procedures.

Good Luck!