Monday, April 16, 2012 6:53 PM
Hi. I have a table A that has an identity column (SQL Server 2008). When a transaction is completed, data from this table A is copied to another table B ("archived") and removed from table A. It works fine since the identity column is auto-incremented as new data is added to table A. The problem I have is that if there is no transaction for a month (I am not 100% sure, it seems to be the case since we have ~100 identical databases and the problem seems to always occur in this situation), the identity is reset and it starts over. It is a problem when I archive the transaction to table B since that ID already exists.
I checked the database and application code and I don't see any dbcc checkident or truncate. The application automatically does a backup every 2 weeks, it is possible that after a backup is done, if there is no data in table A, SQL Server automatically reseeds the column? It doesn't make much sense to me but I don't see another explanation right now. What could reseed an identity besides a dbcc checkident or a truncate?Thanks
Monday, April 16, 2012 7:01 PM
is your identity column restating at the beginning due to reaching the maximum value?
- Edited by Christian C Gräfe Monday, April 16, 2012 7:03 PM
Monday, April 16, 2012 7:06 PMCould it be some job running that does it or another process / application you haven't checked?
For every expert, there is an equal and opposite expert. - Becker's Law
Monday, April 16, 2012 7:16 PM
No it didn't reach the max value (one was reset when it was only at ~350).
It is an application that we designed from scratch about 3 years ago so it is not a legacy project with a bunch of "old not very well known" applications that use the database. There is only one application that uses the database and as I said I don't see a dbcc checkident in it (I'll keep looking as I might have missed it).
Wednesday, April 18, 2012 12:11 PMI am going to continue my investigation, I don't understand the cause so far.
Wednesday, April 18, 2012 12:34 PMModerator
Another posibility besides truncate, drop/create or reseed is SET IDENTITY_INSERT ON.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Wednesday, April 25, 2012 6:46 PMThanks for the info Dan. This is something that I use sometimes but not on that table unfortunately so it cannot be the reason :-(