Failover or Restart Results in Reseed of Identity

Bug with Id: 739013, opened: 4/26/2012

When a table with less than 1000 rows that has an identity value is part of a database that is failed over in an AlwaysOn availability group, the identity is reseeded to 1000. If the identity value is already over 1000 no reseed occurs. This also occurs if you restart the server.

This is happening in Azure databases too.

Example: In a table “X” your Id starts in 1, 2, 3, 4 then suddenly 1005, 1006, etc. And you don’t know when it will happen.

http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity

Some people think this has something to do with Sequences like this:

http://msdn.microsoft.com/en-us/library/ff878091.aspx

One workaround is using the startup flag T272 but how to specify this in Azure?

Also some people say this keeps happening even when you get over 1000 rows.

Advertisements
This entry was posted in Azure and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s