Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Identity key (unique column) not behaving properly Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 7:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:39 AM
Points: 415, Visits: 1,333
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!


Post #1475450
Posted Friday, July 19, 2013 7:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,404, Visits: 36,849
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 2008, MVP
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

Post #1475477
Posted Friday, July 19, 2013 7:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 2,873, Visits: 5,189
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1475487
Posted Friday, July 19, 2013 7:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:39 AM
Points: 415, Visits: 1,333
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.
Post #1475488
Posted Friday, July 19, 2013 7:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,404, Visits: 36,849
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 2008, MVP
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

Post #1475492
Posted Friday, July 19, 2013 8:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:39 AM
Points: 415, Visits: 1,333
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.
Post #1475499
Posted Friday, July 19, 2013 8:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,404, Visits: 36,849
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 2008, MVP
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

Post #1475503
Posted Friday, July 19, 2013 8:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:39 AM
Points: 415, Visits: 1,333
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.
Post #1475525
Posted Friday, July 19, 2013 12:47 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:39 AM
Points: 415, Visits: 1,333
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,
Post #1475644
Posted Monday, August 4, 2014 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:39 PM
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.
Post #1599365
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse