November 2, 2016 at 4:20 pm
We are having random failures in the field where the identity value does not increment correctly. The table has an int primary key marked as an identity, increment 1. This usually works correctly. But sometimes we get a duplicate key error on inserts.
When the error occurs for example:
DBCC CHECKIDENT ('table1', NORESEED)
returns 1453158, but the table has rows with primary keys up to 1453164. There is something seriously wrong about this.
If we run
DBCC CHECKIDENT ('table1')
the identity is updated to the correct value and subsequent inserts are done without error. How can sql server identity values be fouled up like this? I thought we could always depend on the autoincrement mechanism.
November 2, 2016 at 4:28 pm
P a u l (11/2/2016)
How can sql server identity values be fouled up like this?
Like this:
DBCC CHECKIDENT ( 'table1', RESEED, 1453158)
_____________
Code for TallyGenerator
November 2, 2016 at 4:37 pm
Sergiy (11/2/2016)
P a u l (11/2/2016)
How can sql server identity values be fouled up like this?Like this:
DBCC CHECKIDENT ( 'table1', RESEED, 1453158)
Perhaps I did not word the posting correctly. Why does sql server's identity setting get out of sync with the rows in the table? This seems like a bug in sql server to me. Seems unlikely and how could I have caused this?
November 2, 2016 at 5:18 pm
If by out of sync you mean that the numbers look like:
1,2,3,17,18,19
That can be from two causes. Either deletes, or, more likely, rollbacks. If an insert fails, the identity value has already been assigned. You could easily see the next value in the above list be 21 or 22 or some other number because the attempted insert of 20 was rolled back because of some other issue.
If you mean that the seeded value is now 18 in the above sequence, I've only seen that happen when people run the RESEED command. I've never seen it happen any other way. You either have identified a unique bug (pretty unlikely, but possible), or you may have some other process modifying the SEED value of the identity column (very likely). You might want to put extended events on the case to watch for that command.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 2, 2016 at 5:43 pm
Grant Fritchey (11/2/2016)
If by out of sync you mean that the numbers look like:1,2,3,17,18,19
That can be from two causes. Either deletes, or, more likely, rollbacks. If an insert fails, the identity value has already been assigned. You could easily see the next value in the above list be 21 or 22 or some other number because the attempted insert of 20 was rolled back because of some other issue.
If you mean that the seeded value is now 18 in the above sequence, I've only seen that happen when people run the RESEED command. I've never seen it happen any other way. You either have identified a unique bug (pretty unlikely, but possible), or you may have some other process modifying the SEED value of the identity column (very likely). You might want to put extended events on the case to watch for that command.
I don't care about discontinuity. What I don't want to see is a primary key constraint violation on inserts. When this happens the seed value is 18 as in your example, but existing rows already have keys higher than this, 19, 20, etc. How can this happen? I am inserting in a foreach loop using dapper micro-orm. I am not sending any DBCC commands in my code, so not reseeding accidently as far as I know. It's a stand-alone windows app, using sql local db 2012, with no other database users.
Anyway I think I have fixed this rare bug by called DBCC CHECKIDENT ('table1') before inserting a lot of new rows. It's cludgey but I have to fix this somehow.
November 2, 2016 at 5:55 pm
It's either someone reseeding or it's someone doing an identity_insert to your data. Neither is good, but both should be under your control.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply