BigInt, Identity, and Jumping IDs

If you have a table in SQL Server 2012 and you specify one field as bigint with identity set to Yes then you will probably run into jumping IDs should you ever restart the service.

Problem: SQL Server jumps auto increment by 10k records on bigint fields set to identity after a service restart.

1. I created a basic table to recreate the issue:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testIdentity](
[MyBigInt] [bigint] IDENTITY(1,1) NOT NULL,
[MyDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

2. I wrote a little bit of code to quickly insert a few records into the table:

declare @myCounter as int
set @myCounter = 0
WHILE @myCounter < 10
Begin
Insert Into [testIdentity] (MyDate)
Values (DateAdd(Day, @myCounter, GetDate()))
--print DateAdd(Day, @myCounter, GetDate())
set @myCounter = @myCounter + 1
If @myCounter > 10
BREAK
Else
CONTINUE
End 

3. Next, I restarted the SQL Server service for the instance.

4. I ran the above code again and looked at the results:

Right after the restart, SQL Server decided to skip some 10k numbers. The big problem with this is after the first time through this the problem appears intermittently. If you need to keep track of all records then this little feature bug is something you never want to see. It looks like you've deleted a ton of records.

Solution: Add a startup parameter under Configuration Manager.

1. Open SQL Server Configuration Manager
2. Right click the Instance and select Properties (not the Agent service)
3. Click the Startup Parameters tab
4. Under the Specify a startup parameter field enter: -t272
5. Restart the service

eol