Identity Range not working for Queued Updating Replication in SQL Server 2005 and 2008

Identity Range not working for Queued Updating Replication in SQL Server 2005 and 2008 by Jason Qu

Active

Type:	Bug
ID:	777364
Opened:	1/24/2013 2:18:35 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

We have a table on our publisher that seems to be filling up it's identity
ranges periodically. It seems as though when it does fill up its
range of values that it doesn't get its new ranges soon enough and users
experience the error "The insert failed. It conflicted with an identity range
check constraint". I reproduced this error both on SQL 2005 and 2008 editions.

Details (expand)

English

SQL Server 2005 SP4

Replication

Windows Server 2008 (all editions)

US English

0)Create a table with identity column
CREATE TABLE [dbo].[employee](
    [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [name] [char](60) NOT NULL
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

1) Add the table to a queued update replication
2) Set the publisher range size to 1000 and set Range Threshold percentage to 80, which is for reproduce this issue easily
3) run loop insert like below in publisher database, it will error out soon.

--insert data
declare @i int
set @i=1
while (@i<=1000)
begin
    insert into employee(name) select @i
    print @i
    --exec sp_helpconstraint 'employee'
    set @i=@i+1
end

Msg 548, Level 16, State 2, Line 5
The insert failed. It conflicted with an identity range check constraint in database 'TestPub', replicated table 'dbo.employee', column 'id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

Insert data to a table doesn't incur such 'insert failed' error.

X64

0 attachments

Hi Jason,

thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant

Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server