Identity reseeded unintentionally

Question

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

Answer 1

Hello,

is your identity column restating at the beginning due to reaching the maximum value?

best regards

Answer 2

Could 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


My blog

Answer 3

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).



Answer 4

I am going to continue my investigation, I don't understand the cause so far.

Answer 5

Another posibility besides truncate, drop/create or reseed is SET IDENTITY_INSERT ON.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

Thanks for the info Dan. This is something that I use sometimes but not on that table unfortunately so it cannot be the reason :-(