Auto ID jumping issue with SQL 2012

 

I Once came across an issue with the SQL table records insert where the Auto Increment ID jumped by   around 1000 when it is supposed to increase by 1.

For example, let say we have a table for User detail with a primary key column “UserID”. Identity insert is enabled on this column. It was increasing by 1 for some time and suddenly it jumped from 9001 to 10,001 something like that.

I had no clue at all. Search on the net directed me to the following Microsoft thread. After reading it, I realized that every time the SQL server restarted auto ID will jump. As per Microsoft it is a feature in the latest version of SQL server. (SQL 2012).

They have given a work around to get rid of this feature/issue.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1?forum=transactsql

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

What we need to do is to change the settings as given below

1. Open "SQL Server Configuration Manager"
2. Click "SQL Server Services" on the left pane
3. Right-click on your SQL Server instance name on the right pane
4. Click "Properties"
5. Click "Startup Parameters"
6. On the "specify a startup parameter" textbox type "-T272"
7. Click "Add"
8. Confirm the changes

The given work around worked like a charm.

Hope this helps.