Automatic identity management in transactional replication

• Animal Magic

  SSChampion

  Points: 13964

  More actions

  April 19, 2012 at 7:48 am

  #254505

  Hi All,

  Was hoping someone could help me out regarding the auto management of identity ranges in a transactional replication setup.

  Every now and again (and its not often) out application crashes and complains about identity out of range on one of the tables. Nothing has to be done manually and the app just starts working again, with a new identity range in place.

  I decided to look a bit deeper into it as i have looked into it in the past and never got a clear answer as to why my replication agent jobs are not running sp_adjustpublisheridentityrange to maintain the ranges.

  I bumped into this paragraph on technet http://technet.microsoft.com/en-us/library/ms152543.aspx

  If the Publisher exhausts its identity range after an insert, it can automatically assign a new range if the insert was performed by a member of the db_owner fixed database role. If the insert was performed by a user not in that role, the Log Reader Agent, Merge Agent, or a user who is a member of the db_owner role must run sp_adjustpublisheridentityrange (Transact-SQL). For transactional publications, the Log Reader Agent must be running to automatically allocate a new range (the default is for the agent to run continuously).

  Now, previously i had always thought that if the distribution agent was running continuously then the ranges would not be automatically updated, so i run my jobs every 10-20 seconds instead of continuous. Looking at the above though that states that if the insert is not done by a db_owner, which ours isnt, the log reader must run sp_adjustpublisheridentityrange. it then states that the log reader agent must be running which ours is, continous.

  Has anyone any idea when the log reader is meant to run this sp? or do i need to setup the log reader so it stops and starts throughout the day in order for it to run the adjustment SP?

  Note that this is a true managed identity range issue and not just a case of too many inserts hitting the threshold. Our ranges on the table in question are 100,000 with a threshold of 80% and it would normally take a few months for us to hit 80,000 inserts on that table.