Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
sp_adjustpublisheridentityrange (Transact-SQL)
Collapse the table of content
Expand the table of content

sp_adjustpublisheridentityrange (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Adjusts the identity range on a publication and reallocates new ranges based on the threshold value on the publication. This stored procedure is executed at the Publisher on the publication database.

Topic link icon Transact-SQL Syntax Conventions

sp_adjustpublisheridentityrange [ [ @publication = ] 'publication' ]
    [ , [ @table_name = ] 'table_name' ]
    [ , [ @table_owner= ] 'table_owner' ]

[ @publication=] 'publication'

Is the name of the publication in which new identity ranges are reallocated. publication is sysname, with a default of NULL.

[ @table_name=] 'table_name'

Is the name of the table in which new identity ranges are reallocated. table_name is sysname, with a default of NULL.

[ @table_owner=] 'table_owner'

Is the owner of the table at the Publisher. table_owner is sysname, with a default of NULL. If table_owner is not specified, the name of the current user is used.

0 (success) or 1 (failure)

sp_adjustpublisheridentityrange is used in all types of replication.

For a publication which has the auto identity range enabled, the Distribution Agent or Merge Agent is responsible for automatically adjusting the identity range in a publication based on its threshold value. However, if for some reason the Distribution Agent or Merge Agent has not been run for a period of time, and identity range resource have been consumed heavily to the point of threshold, users can call sp_adjustpublisheridentityrange to allocate a new range of values for a Publisher.

When executing sp_adjustpublisheridentityrange, either publication or table_name must be specified. If both or neither are specified an error is returned.

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_adjustpublisheridentityrange.

Community Additions

© 2015 Microsoft