Identity column value suddenly jumps to 1001 in sql server [duplicate]

by

Microsoft has changed the way they deal with identity values in SQL Server 2012 and as a result of this you can see identity gaps between your records after rebooting your SQL server instance or your server machine. There might be some other reasons for this id gaps, it may be due to automatic server restart after installing an update.

You can use below two choices

  • Use trace flag 272
    o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.

  • Use a sequence generator with the NO CACHE setting

    Setting Trace Flag 272 on SQL Server 2012 that you are expecting here

  • Open “SQL Server Configuration Manager”

  • Click “SQL Server Services” on the left pane

  • Right-click on your SQL Server instance name on the right pane ->Default: SQL Server(MSSQLSERVER)

  • Click “Properties”

  • Click “Startup Parameters”

  • On the “specify a startup parameter” textbox type “-T272”

  • Click “Add”

  • Confirm the changes

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Sql to select the rows and ignore the duplicate userid in SQL Server [closed]
  3. Identity increment is jumping in SQL Server database
  4. SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]
  5. get a comma delimited string from rows [duplicate]
  6. T-SQL – Insert Data into Parent and Child Tables
  7. Default row order in SELECT query – SQL Server 2008 vs SQL 2012
  8. Scope of temporary tables in SQL Server
  9. Sequence vs identity
  10. Count Number of Consecutive Occurrence of values in Table
  11. Get previous and next row from rows selected with (WHERE) conditions
  12. Convert Date format into DD/MMM/YYYY format in SQL Server
  13. Replace first occurrence of substring in a string in SQL
  14. Can SQL Server Express LocalDB be connected to remotely?
  15. How to correctly insert newline in nvarchar [duplicate]
  16. Find min and max for subsets of consecutive rows – gaps and islands
  17. Group All Related Records in Many to Many Relationship, SQL graph connected components
  18. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  19. How to subtract 30 days from the current date using SQL Server
  20. Cannot Resolve Collation Conflict
  21. Try_Convert for SQL Server 2008 R2
  22. How to get Previous Value for Null Values
  23. Can’t create stored procedure with table output parameter
  24. SQL Server: +(unary) operator on non-numeric Strings
  25. How to automatically generate unique id in SQL like UID12345678?
  26. CROSS JOIN vs INNER JOIN in SQL
  27. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  28. How to get the identity of an inserted row?
  29. Finding a Top Level Parent in SQL
  30. Convert bigint to datetime

Leave a Comment