Knowledgebase
ACES 4 Hire Help Desk > Help Desk > Knowledgebase

Search help:


SQL Server IDs jump by 1000

Solution

Issue:

If you are using surrogate keys or primary keys in the SQL tables that are set to auto increment, you may notice this issue in production. MS SQL Server, when restarted, auto increments those values by 1000. So if a ID column value was 10112 the next record that gets added after restart will get ID value 11112. Here is a thread on this issue:

MSDN Thread

Resolution (for prevention):

This is indeed a feature of later version of SQL Server. It "caches" 1000 identity values in advance for performance reasons (more on that later).

This can be disabled with a couple of methods.If your data already fell victim to this then you would have to go back and update those records. That's a whole another resolution. But to prevent this from happening in future, here is a resolution.

The "272" trace flag enables logging of identity computations and apparently disabled this behavior, essentially reverting to the older SQL server behavior. Microsoft warns that this might carry a performance penalty, but it shouldn't be significant.

  • 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
 
Was this article helpful? yes / no
Article details
Article ID: 3
Category: SQL Server
Rating (Votes): Article not rated yet (0)

 
« Go back

 
Powered by Help Desk Software HESK, in partnership with SysAid Technologies