Приращение идентичности скачет в базе данных SQL Server

В одном из моих столов Fee в столбце "ReceiptNo" в SQL Server 2012 приращение идентификатора базы данных внезапно начало переходить на 100 с вместо 1 в зависимости от следующих двух вещей.

  1. если оно равно 1205446, оно переходит к 1206306, если оно равно 1206321, оно переходит к 1207306, а если оно равно 1207314, оно переходит к 1208306. Я хочу отметить, что последние три цифры остаются постоянными, т. е. 306 при каждом переходе происходит, как показано на следующем рисунке.

  2. эта проблема возникает при перезагрузке компьютера

Источник

6 ответов

Решение

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

SQL Server 2012 теперь использует размер кэша 1000 при выделении IDENTITY значения в int столбец и перезапуск службы могут "потерять" неиспользуемые значения (размер кэша составляет 10 000 для bigint / numeric).

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

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

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

  1. Вы можете использовать SEQUENCE вместо идентификатора столбца и определить меньший размер кэша, например, и использовать NEXT VALUE FOR в столбце по умолчанию.
  2. Или примените флаг трассировки 272, который делает IDENTITY распределение зарегистрировано как в предыдущих версиях.

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

Источник

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

Решение:

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

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

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

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

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

    Параметры запуска SQL Server

Источник

От SQL Server 2017+Вы можете использовать ALTER DATABASE SCOPED CONFIGURATION:

IDENTITY_CACHE = {ON |OFF}

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

(...)

G. Установите 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).

Hack / Fix:

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

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/

Источник
Другие вопросы по тегам