If you are running SQL Server 2012, you may have experienced a strange behavior related to Identity values. Occasionally, you may see a large increase in the value of a tables Identity column. If the column is of type INT, you would see a value increase of 1,000. If the column is of type BIGINT, you would see a value increase of 10,000. And seeing this strange behavior should concern you (strange behaviors in computers are rarely good). Fortunately though, there is nothing wrong with your code or your database. Microsoft added this "feature" for you.
So technically, this is a feature that Microsoft has added in order to allow quicker inserts by generating the next Identity quicker. But to many, this is annoying at best and a defect at worst. But you can easily "fix" this by implementing the following.
- Open SQL Server Configuration Manager
- Double-Click "SQL Server" under the "SQL Server Services" option on the left an add a new startup parameter "-t272".
- Restart SQL Server.
This should prevent this issue from happening in all databases on your server.