Microsoft KB Archive/321879

= FIX: IDENTITY Columns May Be Incorrectly Reseeded If SQL Server Shuts Down Unexpectedly =

Article ID: 321879

Article Last Modified on 9/27/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q321879



BUG #: 450065 (SHILOH_BUGS)



SYMPTOMS
A table that is published for replication, which uses the IDENTITY property, may be incorrectly reseeded if all of the following conditions are true:


 * A replication agent is in the process of inserting rows to a table when SQL Server shuts down.
 * The table that receives the aforementioned INSERT operation has a column with the IDENTITY NOT FOR REPLICATION property defined.
 * The IDENTITY_INSERT option is set on the database connection that performs the INSERT operation.

If you configure an article to use automatic identity range management, a client application that tries an INSERT to the table may receive Transact-SQL error message 548 after the identity column is reseeded, but before the replication agent runs and assigns a new range to the replica:

Msg 548, Level 16, State 2, Server %s, Line 1 The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database '%s', table '%s', column '%s'. Sp_adjustpublisheridentityrange can be called to get a new identity range. The statement has been terminated.

Note that is is possible to receive error message 548 and not experience the problem documented in this article. To confirm that you are experiencing this problem, verify that a new identity range was assigned to the article before the threshold for the article is exceeded.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix should have the following file attributes or later:

  Date         Time         Version    Size              File name

04-24-2002  8:12:40 PM   8.00.615   7,454,801 bytes   Sqlservr.exe



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.



MORE INFORMATION
If you use automatic identity range management, you will notice that this bug causes a new identity range to be prematurely assigned to the replica where the server shut down occurs.

