When using SpiraTest, SpiraPlan, SpiraTeam or KronoDesk with SQL Server 2012 or later, you may occasionally have a situation where the ID for an artifact (test cases, incident, ticket) suddenly jumps by exactly 1,000 values (e.g. from 214 to 1214 instead of just incrementing to 215).

Why Does this Happen?

SQL Server 2012 now uses a cache size of 1,000 when allocating IDENTITY values and restarting the service "loses" unused values.

If this is for some reason a real issue for you see the workarounds on the linked Microsoft Connect Item thread.

You can use a sequence with a lower cache size for example or trace flag 272 makes the IDENTITY allocation logged as in previous versions.

You should be aware neither of these assure no gaps. IDENTITY has never guaranteed this as it would only be possible by serializing inserts to the table.

Article Info
  • Last Updated: 2/16/2016
  • Article ID: KB189
  • Views: 1890