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:
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
