July 19, 2013 at 7:01 am
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!
July 19, 2013 at 7:26 am
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
July 19, 2013 at 7:38 am
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')
July 19, 2013 at 7:43 am
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.
July 19, 2013 at 7:47 am
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
July 19, 2013 at 8:02 am
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.
July 19, 2013 at 8:06 am
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
July 19, 2013 at 8:45 am
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.
July 19, 2013 at 12:47 pm
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,
August 4, 2014 at 12:39 pm
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.
August 4, 2014 at 1:55 pm
UPDATE
I applied SP2 on one of my SQL 2012 servers and unfortunately, did not fix the issue.
August 4, 2014 at 2:36 pm
ugh, not good news for me. 🙁
we have about 60 databases (all the same basically) and so far this has only happened to 1 table in 1 database.
Will let you know if I uncover anything.
August 5, 2014 at 7:30 am
Consult with the team and consider all the potential routes by which records are inserted into this table. For example, is it always inserted via the application, or is there also an ETL process (ex: SSIS) that periodically bulk loads records using identity insert option ?
You may even want to place a profiler trace on this table to examine what exactly is touching it and try to correlate specific insert events with occurrences of the duplicate key error.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 5, 2014 at 8:09 am
Its always loaded the same way, via the application. No bulk loading, etc... its all user inputted.
out of 60 databases (clients) only 1 has gotten this error so far.
I had our hosting company restore a database from the previous night and I checked the table, and those IDs weren't in there that got errors the next day.
Only 3 people have access to the database, me being one of them. There is NO way for the table to have gotten reseeded.
August 8, 2014 at 11:59 am
We're about to try trace flag T272 as a startup parameter on one of our SQL servers. This trace flag brings the Identity key generator to its previous SQL2008 behaviour, which was different.
I'll post back if this fixes the issue, makes it worse or has no effect.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply