How to – Solve SQL Server 2012 Identity Problem. Identity increased by 1000 or 10000

Are you experiencing an identity problem with your SQL Server 2012? Do the identity seeds of your records get increased by 1000 or 10000(in my case it was 10000)? Do you wonder why this is happening and how you can solve this? Then keep reading…

Seems like Microsoft has changed the way they deal with identity values in SQL Server 2012 and as a result of this you can see identity gaps between your records after rebooting your SQL server instance or your server machine. There might be some other reasons for this id gaps, but in my case it was an automatic server restart after installing an update.
There are several ways to overcome this issue but here I will explain how I did it, which seemed to be the best way of overcoming this problem.
Below is a quote from a Microsoft post replying to this id crisis issue on “connect”:
If you require the same identity generation semantics as previous versions of SQL Server there are two options available:
•         Use trace flag 272
o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
•         Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx)
o This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE.
What I did: Setting Trace Flag 272 on SQL Server 2012
  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 ->Default: SQL Server(MSSQLSERVER)
  4. Click “Properties”
  5. Click “Startup Parameters”
  6. On the “specify a startup parameter” textbox type “-T272”
  7. Click “Add”
  8. Confirm the changes

Now you should be free of this annoying identity gap problem.

Hope this helps someone.

Good luck,
Serdar.

8 thoughts on “How to – Solve SQL Server 2012 Identity Problem. Identity increased by 1000 or 10000

  1. Hello, sorry for the late response, haven’t been around for a while.

    According to Microsoft (as I also stated in my post) 272 will cause a log record to be generated for each generated identity value as in previous versions. This log is what prevents gaps from ever happening.

  2. Can someone please show a way to apply this flag(-T272) to LocalDB version of SQL 2012 Express in order to avoid identity jump for LocalDB-managed database-tables? Thanks – Rajesh

  3. Hey there would you mind letting me know which hosting company you’re
    working with? I’ve loaded your blog in 3 completely different web
    browsers and I must say this blog loads a lot
    faster then most. Can you recommend a good hosting provider at a
    honest price? Cheers, I appreciate it!クロムハーツ メガネ 取扱店

Leave a Reply

Your email address will not be published. Required fields are marked *