none
identity field value jumps to 1000 or 10000 without insert attempts RRS feed

  • Question

  • There has been a case in which the value of identity field has jumped to 1000 or 10000 in multiple tables but without any insert attempt. What could be the reason? 
    Tuesday, July 23, 2019 9:06 AM

Answers

All replies

  • Yes, it happens after you restart the service .This is because SQL Server had generated and cached the next 1000 identity values for the table. SQL Server does this for the improve the performance of the INSERT statements which runs on the table. However, due to the unexpected crash scenario, it also loses the pre-generated identity value and that behavior is the responsible for the 1000 value jump in the identity.

    One solution you can use the startup parameter -T272


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, July 23, 2019 9:08 AM
    Answerer
  • That's a documented behaviour, see CREATE TABLE (Transact-SQL) IDENTITY (Property) => Remarks => Consecutive values after server restart or other failures

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Tuesday, July 23, 2019 9:31 AM
    Moderator
  • Thank you all for the replies. They helped me. But why does the sql server service restart? This may cause serious consequences in production server.
    Tuesday, July 23, 2019 9:39 AM
  • Often cause is a Windows LiveUpdate, which requires a Server reboot.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, July 23, 2019 10:27 AM
    Moderator
  • Thank you all for the replies. They helped me. But why does the sql server service restart? This may cause serious consequences in production server.

    You can check the SQL Server error log to see when it was restarted last time, and if you look at the end of ERRORLOG.1, you can see some of the conditions when it was stopped. I would assume that it was stopped and started manually during a maintenance window.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 23, 2019 11:24 AM