SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4697 Visits: 1930
UPDATE

I applied SP2 on one of my SQL 2012 servers and unfortunately, did not fix the issue.
kschlege
kschlege
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 23
ugh, not good news for me. Sad

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.
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29937 Visits: 11564
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
kschlege
kschlege
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 23
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.
sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4697 Visits: 1930
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.
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29937 Visits: 11564
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
kschlege
kschlege
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 23
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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26545 Visits: 12506
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>Wink 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 :heheSmile 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
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4697 Visits: 1930
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
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4697 Visits: 1930
-- 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.
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