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 ««123»»

Identity key (unique column) not behaving properly Expand / Collapse
Author
Message
Posted Monday, August 4, 2014 1:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:36 AM
Points: 384, Visits: 1,269
UPDATE

I applied SP2 on one of my SQL 2012 servers and unfortunately, did not fix the issue.
Post #1599384
Posted Monday, August 4, 2014 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 11:47 AM
Points: 6, Visits: 17
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.
Post #1599401
Posted Tuesday, August 5, 2014 7:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:39 PM
Points: 1,657, Visits: 4,740
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.
Post #1599701
Posted Tuesday, August 5, 2014 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 11:47 AM
Points: 6, Visits: 17
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.
Post #1599730
Posted Friday, August 8, 2014 11:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:36 AM
Points: 384, Visits: 1,269
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.
Post #1601294
Posted Friday, August 8, 2014 1:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:39 PM
Points: 1,657, Visits: 4,740
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.

Post #1601319
Posted Friday, August 8, 2014 2:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 11:47 AM
Points: 6, Visits: 17
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.
Post #1601328
Posted Friday, August 8, 2014 3:03 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 8,826, Visits: 9,384
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 ) if only he'd remembered to add
DBCC CHECKIDENT(&lt;tablename&gt;, RESEED) ;

after the bit of script for each table with an identity column.


Tom
Post #1601337
Posted Friday, August 8, 2014 4:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:36 AM
Points: 384, Visits: 1,269
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.

Post #1601349
Posted Thursday, August 14, 2014 7:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:36 AM
Points: 384, Visits: 1,269
-- 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.
Post #1603266
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse