Greetings all. I hope you are doing well.

I came across very unusual issue with auto increment-ed values whereby it jumped up by 999 instead of 1. I have checked to make sure that column in the table is setup to auto increment and that the seed value is 1 so it should only increment by 1 at a time.

To illustrate the point I had three records in the database with respective ID value of 1, 2 and 3. The next record I inserted gained an ID of 1002!? instead of 4.

Thats not all. I have my code setup so that it creates a content item just before it creates a record in my table. Looking at content item records I have and increment of 995 between two records (1559 to 2554). And Content item metadata records also jumped up from 1228 to 2223 (995).

As funny as it may sound next few records went in just fine with proper increments.

I also haven't noticed anything strange with increment values in Files and Journal tables.

After a quick search I found a few reference where people were having issue with auto incremented values using DAL2. But all I came across pointed to code throwing errors. That is not the case here. Everything is working away happily, no errors, no warnings.

Has anyone else came across this rather strange behaviour with increment values? This the second time I've had this issue. While it is not a game breaker but I'd like to know what I am doing wrong or why this is happening.

BTW I am working with DNN 7.3.1 and DNN 7.3.3

Thanks Sebastian. Prompt reply as usual.

Apologies for my dodgy 'seed' I am by no means SQL Server expert. But yes auto increment is defined as identity to start from 1 and increment by 1 each time (I think :p).

[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [ID] )

I came across a few similar posts to the one you linked me to.  Question poster was able to see a recurring pattern. Also according to one of the answers given this may be an issue with SQL Server dropping its IDENTITY value when service is restarted !? (if I understood that correctly).

But I can safely say that nor SQL Server nor IIS nor IIS Pools were restarted/recycled when this happened because I was inserting records manually using user interface during demonstration to my colleague. And I couldn't have inserted ~1000 records.

Like I said before its not a game breaker and I don't see this issue occurring all that often. I just thought that maybe someone came across the same issue and had some experience to share.

According to this bug report the issue in question affects SQL Server 2012.