Why do I have a thousand entities gap in my Targetprocess IDs?

You may recognize from time to time that when you add an item and then another one, they have a thousand gap in between. For example, you create a story of ID 5500, and then you add one more. For some reason, the second story has ID of 6500, not 5501. If you are a Targetprocess administrator, you would expect that some content has been removed, occupying those IDs. So you open Settings – Audit History and look for the missing entities. However, there is no evidence of those… If you are running our local installation, you may also analyze the database in search of any history table that keeps track of those IDs from 5501 to 5599. But no luck again! The solution seems to be a mistery.

The answer is as simple as this: any failed transaction may lead to all of the IDs involved being lost. For instance, you insert 2000 records in a single statement, and the 1000th fails. In this case, you will lose the next 1000 from the IDs sequence.

The above problem has been registered as a known issue of Microsoft SQL Server 2012. Microsoft declares it is a feature rather than a bug and in many scenarios it could be helpful. However, from our experience, it’s not good at all.

Two main ways to prevent identity gap are known: trace flag 272 and sequence generator with no cache setting. So far, neither of the above is suitable for Targeprocess due to performance problems and code limitations.

That being said, we acknowledge that there isn’t much we can do about the case. Not going through batch actions with the entities would help, but we understand it makes little sense if you have many people and a huge number of working scenarios to run through.