Identity key (unique column) not behaving properly

Question

Identity key (unique column) not behaving properly

Viewing 15 posts - 16 through 30 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

sql-lover (8/8/2014)
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.

Reading on this trace flag, it seems to disable a feature in 2012+ where gaps can result in identity incrmenting when the server is restarted. But that's doesn't seem to correspond to your specific issue of SQL Server seeming to reset identity seed backward during normal operation.

You may want to setup an event audit to determine if something like DBCC RESEED or "set identity_insert on" is being used.

http://msdn.microsoft.com/en-us/library/cc280663(v=sql.110).aspx

Also, confirm that only members of the DBA team have membership in a sysadmin or db_owner role.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

kschlege SSC Veteran Points: 249 More actions · Answer 2

The accounts for connecting to the database have db_owner rights, but don't get me started on that ...

So far this has been only a 1 time occurrence that lasted for 2 days. Unfortunately by time I was told, the error stopped happening.

I did some more checking, and the database is setup just like all our other databases.

Nowhere in our code would we turn off identity columns or reseed the identity.

Just waiting now for it to happen again so I can see what's going on.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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.

Actually that depends on how the migration is done.

I saw someDB by generating a script for it to get the schema set up and all teh UDFs and SPs created, running that, and then generating a script containing something like set identity_insert <tablename> ON ;

insert <schema>.<tablename>(<list of all columns>) select * from <oldserver>.<databasename>.<schema>.<tablename> ;

set identity_insert <tablename> OFF ;for each table with an identity column and the same without the identity insert on/off commands for each other table.

It would have worked (although it seems a bit of a crazy way to do it :hehe:) if only he'd remembered to add DBCC CHECKIDENT(<tablename>, RESEED) ;after the bit of script for each table with an identity column.

Tom

sql-lover SSCoach Points: 18530 More actions · Answer 4

Eric M Russell (8/8/2014)
sql-lover (8/8/2014)
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.
Reading on this trace flag, it seems to disable a feature in 2012+ where gaps can result in identity incrmenting when the server is restarted. But that's doesn't seem to correspond to your specific issue of SQL Server seeming to reset identity seed backward during normal operation.
You may want to setup an event audit to determine if something like DBCC RESEED or "set identity_insert on" is being used.
http://msdn.microsoft.com/en-us/library/cc280663(v=sql.110).aspx
Also, confirm that only members of the DBA team have membership in a sysadmin or db_owner role.

Correct, but we are hoping that bringing this to old SQL2008/2005 behaviour, where we never experienced issues for years, may correct the problem. And if that's the case, I certainly will call MS and inform.

sql-lover SSCoach Points: 18530 More actions · Answer 5

-- Another UPDATE ---

After testing on my Dev box I finally deployed the trace flag on two of our live servers. So far so good. And crossing fingers, it seems that it also fixes my particular issue.

I would like to wait one week at least and if our Development department report no issues from any of these two boxes, I will consider this a workaround on my particular problem too.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

sql-lover (8/14/2014)
-- Another UPDATE ---
After testing on my Dev box I finally deployed the trace flag on two of our live servers. So far so good. And crossing fingers, it seems that it also fixes my particular issue.
I would like to wait one week at least and if our Development department report no issues from any of these two boxes, I will consider this a workaround on my particular problem too.

If this particular error hasn't presented itself again after one more week, will you give credit to the trace flag or to the crossing of fingers? 😉

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

kschlege SSC Veteran Points: 249 More actions · Answer 7

sql-lover (8/14/2014)
-- Another UPDATE ---
After testing on my Dev box I finally deployed the trace flag on two of our live servers. So far so good. And crossing fingers, it seems that it also fixes my particular issue.
I would like to wait one week at least and if our Development department report no issues from any of these two boxes, I will consider this a workaround on my particular problem too.

Keep us updated 🙂

Like I said, there is no way for any identity to be turned off or reseeded, so there is an issue somewhere in 2012, just not sure where ... So it's not just you.

It was only for 2 days we had an issue, and haven't had it since.

sql-lover SSCoach Points: 18530 More actions · Answer 8

Eric M Russell (8/14/2014)
sql-lover (8/14/2014)
-- Another UPDATE ---
After testing on my Dev box I finally deployed the trace flag on two of our live servers. So far so good. And crossing fingers, it seems that it also fixes my particular issue.
I would like to wait one week at least and if our Development department report no issues from any of these two boxes, I will consider this a workaround on my particular problem too.
If this particular error hasn't presented itself again after one more week, will you give credit to the trace flag or to the crossing of fingers? 😉

Both, lol ... I usually get better results when crossing fingers, lol ..

sql-lover SSCoach Points: 18530 More actions · Answer 9

Yep!!!

It fixed the issue! 😛

I deployed on one of my production servers few days ago. Later this week I will set on remaining ones.

So for anyone experiencing this issue, that did not have have before on SQL 2005 or SQL 2008, I strongly suggest check this a possible root cause and workaround.

Now, I wonder what MS is planning to do about this. This new Identity behavior won't go away on SQL 2014. They need to fix it. It is not working properly for some. Using a trace flag , in my opinion, is a workaround but not a solution.

kschlege SSC Veteran Points: 249 More actions · Answer 10

Just got the error on another database for another client for a completely different table on Friday.

Violation of PRIMARY KEY constraint 'PK__USER_REPORT_PROCESS_PARAMETER__ID'. Cannot insert duplicate key in object 'dbo.USER_REPORT_PROCESS_PARAMETER'. The duplicate key value is (37797). The statement has been terminated.

adrian.buzila Ten Centuries Points: 1116 More actions · Answer 11

I have the same problem with SQL2012 (11.0.5058.0) on many tables.

I checked for reseed and set identity_insert. It's not the case.

If there is a unique constraint insert fails. It not sql server inserts duplicates.

@SQL-Lover Thanks for posting.

kschlege SSC Veteran Points: 249 More actions · Answer 12

Since this has happened on 2 different databases, I'm thinking that if I review all my log files, it's happened more than just twice.

It also happened on 2 different tables too, so there isn't any rhyme or reason to it either.

sql-lover SSCoach Points: 18530 More actions · Answer 13

adrian.buzila (9/23/2014)
I have the same problem with SQL2012 (11.0.5058.0) on many tables.
I checked for reseed and set identity_insert. It's not the case.
If there is a unique constraint insert fails. It not sql server inserts duplicates.
@SQL-Lover Thanks for posting.

No problem.

It may help others if you mark my last post (the trace flag) as solution.

Thanks.

adrian.buzila Ten Centuries Points: 1116 More actions · Answer 14

My DBA (I'm developer) did not agree with the flag, said it will impact performance, sql2008 identity is more resource intensive.

I implemented you first solution with reseed max value. It will take about one week to say is ok (this is the average frequency of duplicates)

As a particularity it happends on tables with a lot of columns. One with 760 columns (318 nvarchar, 143 uniqueidentifier, 100 datetime, 97 bit, 72 float 18 int, 10 decimal, 2 tinyint), another with 177 columns. What about you, do you find any particularity?

One insert is issued using exec(@sql) another one uses MERGE. So this is not a particularity.

kschlege SSC Veteran Points: 249 More actions · Answer 15

I don't think you'll get many DBAs to agree to add the flag, but unless we can get Microsoft to agree this is a defect, I doubt it'll get fixed anytime soon. Unless they already know about this issue.