Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identity key (unique column) not behaving properly


Identity key (unique column) not behaving properly

Author
Message
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
Hi,

Wonder if someone has seen this issue before or if I am missing any recent hotfix here that I should apply.

I've seen this error on several of my databases again and again this week:


Violation of PRIMARY KEY constraint 'PK_XXXX'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (whatever number here).
The statement has been terminated.


The thing is, the PK is unique, and the INSERT statement does not touch that column, it touches the other ones. So SQL2012 is the one that automatically generates the next and unique available value.

How can be possible that the value to be inserted (generated by SQL2012) is a duplicate one? By the way, this was not happening on the previous SQL2005 or SQL2008 server where the databases were being hosted.

Here's my SQL 2012 version: 11.0.3000.0 (SP1 applied). Runs on top of a Win2008R2 Cluster.

Any hints are highly appreciated!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47404 Visits: 44399
Identity has never guaranteed unique numbers, just incrementing numbers. If the identity gets reseeded, SQL will start from the given number regardless of whether that number is already in the table or not.
Identity insert also can put values in that the identity doesn't know about.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3046 Visits: 5478
Something reseeded your identity. Check this sample:


CREATE TABLE TestA (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY , val VARCHAR(10));

insert TestA values ('a')
insert TestA values ('b')
insert TestA values ('c')
insert TestA values ('d')
insert TestA values ('e')

delete TestA where id < 4

DBCC CHECKIDENT ('TestA', RESEED, 0);

insert TestA values ('aa')
insert TestA values ('bb')
insert TestA values ('cc')
-- fine so far...

-- here is Oops!
insert TestA values ('dd')




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
GilaMonster (7/19/2013)
Identity has never guaranteed unique numbers, just incrementing numbers. If the identity gets reseeded, SQL will start from the given number regardless of whether that number is already in the table or not.
Identity insert also can put values in that the identity doesn't know about.


Hi Gail,

Thanks for reply.

I have not reseed the values. Unless migrating the db did. I moved it from old SQL box to our new SQL2012 Cluster.

But, assuming that was the case, will reset the value to current existing upper one, fix the issue?

Also, I forgot to mention. The issue is happening on several of my databases so it's not contained to just one table or database.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47404 Visits: 44399
Migrating a DB does not reseed identities. Check for jobs that may have done so, ask other DBAs, check the max value in the table and compare it to the current identity seed.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
GilaMonster (7/19/2013)
Migrating a DB does not reseed identities. Check for jobs that may have done so, ask other DBAs, check the max value in the table and compare it to the current identity seed.


Yep!

Ran DBCC CHECKIDENT and the current column value looks like is higher than the current identity value so it will fail because next one is there.

The thing is, I am the only DBA here and I have not reseed the values. I've been told by my Development team that they have not either. So now I'm puzzled now about what or how this was changed.

I'll post later to confirm.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47404 Visits: 44399
sql-lover (7/19/2013)
Ran DBCC CHECKIDENT and the current column value looks like is higher than the current identity value so it will fail because next one is there.


Suggests that someone, sometime for some reason has reseeded the identity value below what's in the column. Finding out who however may prove difficult, I don't think it's logged anywhere. Maybe the default trace.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
This is going to be a very interesting problem here at my job.

Of course I fixed it (for that particular table), but just had a conversation with one of the Developers and he was firm in saying that no one manually lower the seed value (reseed) or increased the actually ID on that column, I guess using IDENTITY INSERT somewhere in the INSERT code? And I truly believe him. Same way that I explained to him I have not reseed those values myself.

Very coincidental that this is happening only on the SQL 2012 instance and never happened before though.

I honestly can't remember of any other way to make these two values different than using DBCC CHECKIDENT or manually forcing and changing the upper existing value on that column.
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
I still do not know how those values changed but i modified an existing T-SQL script that I found via Google so I can proactively check for the same issue and fix before the error pops up again, breaking our application.

Here's the code:



USE master;

DECLARE @SQLSearch varchar(1000), @CurID int, @MaxID int, @CurSQL varchar(max)
DECLARE @t TABLE (ID int identity(1,1), sqlstring varchar(max))


insert into @t
select '
USE [' + name + '];
DECLARE @SchemaName nvarchar(128),
@TableName nvarchar(128),
@ColumnName nvarchar(128)
DECLARE @MyTableVar table(
ID bigint IDENTITY (1,1) PRIMARY KEY CLUSTERED,
SchemaName nvarchar(128),
TableName nvarchar(128),
ColumnName nvarchar(128),
object_id int,
last_value sql_variant,
Count bigint,
LastUsedIDValue bigint,
CountDifference bigint,
LastIDDifference bigint,
GeneratedDBCCCode nvarchar(max),
MaxID bigint
)
INSERT INTO @MyTableVar
(SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, CountDifference, LastIDDifference)
SELECT SCHEMA_NAME(schema_id) AS SchemaName, T1.name AS TableName, T2.name AS ColumnName, T2.object_id,
last_value, T3.Rows AS Count, ident_current(SCHEMA_NAME(schema_id) + ''.'' + ''['' + T1.name + '']'') AS LastUsedIDValue,
CAST(last_value AS bigint) - T3.Rows AS CountDifference, CAST(last_value AS bigint) - CAST(ident_current(SCHEMA_NAME(schema_id) + ''.'' + ''['' + T1.name + '']'') AS bigint) AS LastIDDifference
FROM sys.tables AS T1
INNER JOIN sys.identity_columns AS T2
ON T1.object_id = T2.object_id
INNER JOIN dbo.SysIndexes AS T3
ON T3.ID = T1.object_ID
WHERE TYPE_DESC = ''USER_TABLE''
AND is_identity = 1
AND T3.IndID < 2
AND OBJECTPROPERTY(T1.object_ID,''IsMSShipped'') = 0
DECLARE @CurrentTableVarID bigint = 0,
@MaxTableVarID bigint = 0,
@CounterCheck bigint = 0,
@SQLString nvarchar(max),
@ParameterDefinition nvarchar(500),
@MaxID bigint,
@MaxIDOut bigint
SELECT @MaxTableVarID = Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID ASC
SELECT @CurrentTableVarID =Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID DESC
WHILE @CurrentTableVarID <= @MaxTableVarID
BEGIN

SELECT @SchemaName = SchemaNAme, @TableName = TableName,
@ColumnName = ColumnName
FROM @MyTableVar
WHERE ID = @CurrentTableVarID

SET @ParameterDefinition = ''@MaxIDOut bigint OUTPUT'';
SET @SQLString = ''SELECT @MaxIDOut = Max('' + @ColumnName + '') FROM ['' + @SchemaName + ''].['' + @TableName + ''] GROUP BY '' + @ColumnName + '' ORDER BY '' + @ColumnName + '' ASC''

EXEC sp_executesql @SQLString, @ParameterDefinition, @MaxIDOut = @MaxID OUTPUT

UPDATE @MyTableVar
SET MaxID = @MaxID
WHERE ID = @CurrentTableVarID

/*
INSERT INTO @MaxIDTableVar
(ParentID, MaxID)
EXEC [dbo].[ReturnColumnMaxSP]
@SchemaName = @SchemaName,
@TableName = @TableName,
@ColumnName = @ColumnName,
@ParentID = @CurrentTableVarID
*/

SET @CounterCheck = @CounterCheck + 1
SET @CurrentTableVarID = @CurrentTableVarID + 1 -- increment the loop
END

SELECT [DatabaseName] = ''' + name + ''', SchemaName, TableName, ColumnName, last_value, LastUsedIDValue, MaxID, CAST(last_value AS bigint) - MaxID AS MaxIDDifference,
''DBCC CHECKIDENT ('''''' + SchemaName + ''.'' + TableName + '''''' , RESEED, '' + CAST(MaxID AS nvarchar(50)) + '')'' AS GeneratedDBCCCode
FROM @MyTableVar
WHERE CAST(last_value AS bigint) - MaxID < 0
ORDER BY MaxIDDifference DESC

'
from sysdatabases
where name not in ('master','model','msdb')

SELECT @CurID = Min(ID), @MaxID = Max(ID)
FROM @t

-- Looping across databases
WHILE @CurID <= @MaxID
BEGIN
SET @CurSQL = (SELECT sqlstring from @t where ID = @CurID)

EXEC (@CurSQL)

SET @CurID = @CurID + 1
END




I tested on my VMware environment and it worked. I recommend doing the same and test before run it on production. If anyone can improve, feel free to do it. My T-SQL skills are not so good as others, that's for sure.

I ran above code on production, changing the NOT IN to IN , and checking one database. It takes a while depending of the database size but will identify any seed issue in advance,
kschlege
kschlege
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 23
We just ran into this on a production database last week on a table, but only 1 database and only 1 table so far.

The problem "went away" after the users got past the "duplicate values".

We're also running SQL Server 2012. Never had an issue with 2005 or 2008.

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

No one would have reseeded the identity column.

That script has a bug too ... you need to add this below the SET @SQLString


SET @MaxIDOut = 0
SET @MaxID = 0



SQL Server doesn't set values if the query doesn't return any values. I personally think this is a bug, but I always code around it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search