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…
• 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.
- 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 ->Default: SQL Server(MSSQLSERVER)
- Click “Properties”
- Click “Startup Parameters”
- On the “specify a startup parameter” textbox type “-T272”
- Click “Add”
- Confirm the changes
Now you should be free of this annoying identity gap problem.
Hope this helps someone.