Why this is happening ?

Is there a way to fix it ?

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. -  ALWAYS ON SQL Server 2014

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Douglas Foong

Database Engineer, Technology

• Edited by douglas foong Friday, January 30, 2015 6:59 AM

Hi Douglas,

As mentioned in the connect item you have post, the issue is related to the new identity feature from SQL Server 2012 which is designed to boost the performance for high end machines. From SQL Server 2012, we introduce preallocation for identity value.

The identity properties are stored separately in metadata. If a value is used in identity and increment is called, then the new seed value will be set. The operations such as Rollback, Failover can change the seed value except DBCC reseed.

Currently, there is no fix for this issue, however, you can work around this issue by using one of the following options.

1. Add -T272 in SQL Server startup parameters to disable the new identity feature.
2. Create trigger or cursor to do a reseeding of identity as discussed in the connect item.

There are also two blogs about the issue that failover or restart results in reseed of identity for your reference.
https://binary-stuff.com/post/failover-or-restart-results-in-reseed-of-identity---fix
https://multidimensionalmayhem.wordpress.com/2012/10/16/another-stop-gap-solution-for-the-sql-server-2012-identity-crisis/

Thanks,
Lydia Zhang