IEA Software, Inc.

IEA Software Knowledge Base - ID:60043

IEA Knowledge base

Emerald (KB ID: 60043)

Mar 16 2021

Large gaps appear in sequence of account numbers in my Emerald database after SQL Server is shutdown. What causes this and how can it be corrected?

Mar 16 2021

SQL Server 2012 and later include a feature to cache a set of sequence numbers to memory. When the database is shutdown unused sequences in the cache may not be returned for reuse leaving unnecessary gaps in sequencing of account numbers.

To prevent this from happening the caching feature must be disabled.

SQL Server 2012 thru 2016 sequence caching is disabled by applying trace flag 272 to startup parameters of your database server. See SQL Server documentation for applying trace flags for your database and platform.

SQL 2017 and later from Emerald Admin / Database / SQL Server execute the following query to disable sequence caching: