Why identity values can jump by the 1000’s ? Is it normal?

It must have happened you at least once to find identity columns with values that from one row to the next one jump by 1000’s rows, of course without having anyone deleting the rows in between, how this occurs?

Identity columns don’t actually recalculate every time the next number to be inserted, SQL Server caches a number of values in order to obtain faster inserts and polls from there. Just imagine, if for every row you would have to calculate the identity value based on the previous one, it would add a (however small) overhead to every insert, that will grow linearly with the number of rows in the insert.

The (absolutely unofficial and undocumented) number of values cached in “recent” versions of SQL Server for an int identity is 1000, with a bigint identity preallocating 10.000 values, on the opposite scale smallint and tinyint cache respectively 100 and 10 values.

So, any event that makes you lose the in memory cached values for identity columns the value will start from the value just next the latest cached one.
This can happen in case of a power failure, a crash, a failover, or even by simply restarting the service (here the now useless connect item).
So, yes, is normal, or rather, it can happen.

If you really don’t want this kind of behavior, there is a trace flag to disable Identity Caching, 272, which works only globally; Enabling it means disabling identity caching for the whole instance, the performance impact can be negligible or measurable, depending on your type of workload, so don’t forget to always test for your workload.
Starting from SQL Server 2017 there is a database scoped configuration called IDENTITY_CACHE, which disables the identity cache only for the database where it’s enabled, which is definitely more controllable.

Other IDENTITY behavior to remember

Even if you disabled identity caching, you can’t count on Identity columns to be gap-less by design, as there are other patters that will create gaps, for example:

• If identity values are assigned during a transaction, but that transaction is then rolled back, those values aren’t “released” but are never to be seen again, the next value will be the last (unassigned) plus the increment you chose in the identity declaration. You can try it yourself by running this code in SSMS:

USE tempdb;
GO

CREATE TABLE TestMyIdentity([TestMe] INT IDENTITY(1, 1));

BEGIN TRAN;
GO

INSERT INTO TestMyIdentity
DEFAULT VALUES;
GO 10

SELECT *
FROM   TestMyIdentity;

ROLLBACK TRAN;

INSERT INTO TestMyIdentity
DEFAULT VALUES;

SELECT *
FROM   TestMyIdentity;

DROP TABLE TestMyIdentity;
GO

• On deletion, the Identity value of the row is not restored and will not be used again

When working with identities, remember all the above the next time you expect an uninterrupted list of numbers!