Приращение удостоверения прыгает в базе данных SQL Server

Вы сталкиваетесь с таким поведением из-за повышения производительности по сравнению с SQL Server 2012.

Теперь по умолчанию используется размер кеша 1000 при выделении значений IDENTITY для столбца int, а перезапуск службы может «потерять» неиспользуемые значения (размер кеша составляет 10 000 для bigint/numeric).

Это упоминается в документация

SQL Server может кэшировать значения идентификаторов из соображений производительности, а некоторые назначенные значения могут быть потеряны во время сбоя базы данных или перезапуска сервера. Это может привести к пробелам в значении идентификатора при вставке. Если пробелы неприемлемы, приложение должно использовать собственный механизм для генерации значений ключей. Использование генератора последовательности с параметром NOCACHE может ограничить промежутки транзакциями, которые никогда не фиксируются.

Судя по показанным вами данным, это произошло после ввода данных за 22 декабря, а затем при перезапуске SQL Server зарезервировал значения 1206306 - 1207305. После ввода данных за 24-25 декабря был сделан еще один перезапуск, и SQL Server зарезервировал следующий диапазон 1207306 - 1208305, видимый в записях, для 28-го числа.

Если вы не перезапускаете службу с необычной частотой, любые «потерянные» значения вряд ли окажут существенное влияние на диапазон значений, разрешенных типом данных, поэтому лучшая политика — не беспокоиться об этом.

Если по какой-то причине это реальная проблема для вас, некоторые возможные обходные пути...

1. Вы можете использовать SEQUENCE вместо столбца идентификаторов и определить кэш меньшего размера. size, например, и используйте NEXT VALUE FOR в столбце по умолчанию.
2. Или примените флаг трассировки 272, который регистрирует выделение IDENTITY как в версиях до 2008 R2. Это применимо глобально ко всем базам данных.
3. Или, для последних версий, выполните ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF, чтобы отключить кэширование идентификаторов для конкретной базы данных.

Вы должны знать, что ни один из этих обходных путей не гарантирует отсутствие пробелов. Это никогда не гарантировалось IDENTITY, так как это было бы возможно только путем сериализации вставок в таблицу. Если вам нужен столбец без зазоров, вам нужно использовать решение, отличное от IDENTITY или SEQUENCE.

Эта проблема возникает после перезапуска SQL Server.

Решение:

• Запустите Диспетчер конфигурации SQL Server.

• Выберите Службы SQL Server.

  

• Щелкните правой кнопкой мыши SQL Server и выберите Свойства.

• В открывшемся окне в разделе Параметры запуска введите -T272 и нажмите Добавить, затем нажмите кнопку Применить и перезапустите.

  

Из SQL Server 2017+ вы можете использовать ИЗМЕНИТЬ КОНФИГУРАЦИЮ ОБЛАСТИ БАЗЫ ДАННЫХ:

IDENTITY_CACHE = { ВКЛ | ВЫКЛ.

Включает или отключает кеш идентификации на уровне базы данных. По умолчанию включено. Кэширование идентификаторов используется для повышения производительности INSERT в таблицах со столбцами идентификаторов. Чтобы избежать пробелов в значениях столбца Identity в случаях, когда сервер неожиданно перезагружается или переключается на дополнительный сервер, отключите параметр IDENTITY_CACHE. Этот параметр аналогичен существующему флагу трассировки SQL Server 272, за исключением того, что его можно установить на уровне базы данных, а не только на уровне сервера.

(...)

Г. Установить IDENTITY_CACHE

В этом примере отключается кеш идентификации.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Я знаю, что мой ответ может опоздать на вечеринку. Но я решил по-другому, добавив хранимую процедуру запуска в SQL Server 2012.

Создайте следующую хранимую процедуру в главной БД.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit

END

Затем добавьте его в «Пуск», используя следующий синтаксис.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

Это хорошая идея, если у вас мало столов. но если вам нужно сделать для многих таблиц, этот метод все еще работает, но это не очень хорошая идея.

Это все еще очень распространенная проблема среди многих разработчиков и приложений независимо от их размера.

К сожалению, приведенные выше предложения не исправляют все сценарии, т. Е. Виртуальный хостинг, вы не можете полагаться на то, что ваш хост установит параметр запуска -t272.

Кроме того, если у вас есть существующие таблицы, в которых эти столбцы идентификаторов используются в качестве первичных ключей, это ОГРОМНОЕ усилие, чтобы удалить эти столбцы и воссоздать новые, чтобы использовать обходной путь последовательности BS. Обходной путь Sequence хорош только в том случае, если вы разрабатываете новые таблицы с нуля в SQL 2012+.

Суть в том, что если вы используете Sql Server 2008R2, то ОСТАВАЙТЕСЬ НА НЁМ. Серьезно, держись. Пока Microsoft не признает, что они внесли ОГРОМНУЮ ошибку, которая все еще существует даже в Sql Server 2016, мы не должны обновляться, пока они не овладеют ею и не ИСПРАВЯТ ЭТО.

Microsoft прямо представила критическое изменение, то есть они сломали работающий API, который больше не работает должным образом, из-за того, что их система забывает свою текущую личность при перезапуске. Кэш или нет, это неприемлемо, и разработчик Microsoft по имени Брайан должен владеть им, вместо того, чтобы рассказывать миру, что это «по замыслу» и «фича». Конечно, кэширование — это функция, но потеря следа того, какой должна быть следующая идентичность, НЕ ЯВЛЯЕТСЯ ФУНКЦИЕЙ. Это чертов БАГ!!!

Я поделюсь обходным решением, которое я использовал, потому что мои БД находятся на серверах общего хостинга, а также я не удаляю и не воссоздаю свои столбцы первичного ключа, это было бы огромным PITA.

Вместо этого это мой позорный хак (но не такой позорный, как эта ошибка POS, которую представила Microsoft).

Взломать/Исправить:

Перед вашими командами вставки просто обновите свою личность перед каждой вставкой. Это исправление рекомендуется только в том случае, если у вас нет административного контроля над вашим экземпляром Sql Server, в противном случае я предлагаю выполнить повторное заполнение при перезапуске сервера.

declare @newId int -- where int is the datatype of your PKey or Id column
select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME
DBCC CheckIdent('YOUR_TABLE_NAME', RESEED, @newId)

Просто эти 3 строки непосредственно перед вашей вставкой, и все должно быть хорошо. Это действительно не сильно повлияет на производительность, т.е. будет незаметно.

Удачи.

Есть много возможных причин скачка значений идентификатора. Они варьируются от откатных вставок до управления идентификацией для репликации. Что вызывает это в вашем случае, я не могу сказать, не проведя некоторое время в вашей системе.

Однако вы должны знать, что ни в коем случае вы не можете считать столбец идентификаторов непрерывным. Есть слишком много вещей, которые могут вызвать пробелы.

Вы можете найти немного больше информации об этом здесь: http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/