SQL server 2012 – fail/restart results in reseed of identity

11 Nov

In MS SQL Server 2012 we experience jump in identity column when something fails in the database or a restart of the instance – jumps 1000. In order to fix this issue you have to add a trace flag:

  1. Open “SQL Server Configuration Manager”
    * Click”SQL Server Services” on the left pane
    * Right-click on your SQL Server instance name on the right pane
    * Click “Properties”
    * Click “Startup Parameters”
    * On the “specify a startup parameter” textbox type “-T272”
    * Click “Add”
    * Confirm the change
  2. Restart service

 

You can read more

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

http://sqlservice.se/sv/start/blogg/updated-microsoft-sql-server-trace-flag-list.aspx

http://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry

http://www.sqlserver-training.com/sequence-breaks-gap-in-numbers-after-restart-sql-server-gap-between-numbers-after-restarting-server/-

http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server

 

Advertisements

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

%d bloggers like this: