1

I have an Azure SQL database, which contains around 20 tables. I just saw that one of them that the identity field (primary key) is incrementing by 1 for a while and at certain point it jumps with a random number. This happens at two places. First with 1029 and then with 997. They are not related to deleted records or using the CHECKIDENT RESEED.

Any ideas how can I check what causes it?

rene
  • 41,474
  • 78
  • 114
  • 152
Miro J.
  • 4,270
  • 4
  • 28
  • 49
  • IDENTITY will increment if you stage a transaction, but don't commit it. – Eric Hauenstein Jul 22 '13 at 18:45
  • 1
    [This](http://stackoverflow.com/questions/17012339/windows-azure-sql-database-identity-auto-increment-column-skips-values) may help. See, you can get into the sequence gap trouble on SQL Server 2012, especially if hosted by Azure. – OzrenTkalcecKrznaric Jul 22 '13 at 18:57
  • Thanks Orzen! I am glad I am not the only one (not going crazy). I hope MS addresses it soon. – Miro J. Jul 22 '13 at 19:15

2 Answers2

0

If you have a transaction that inserts records, but the transaction is rolled back the identity seed still increments. Maybe you had some instances where something failed and was rolled back?

dazedandconfused
  • 3,131
  • 1
  • 18
  • 29
  • I realize that and I am sure there were no 1029 and 997 failures in a row. The DB is used only by my laptop as it is a test DB. Any other suggestions? – Miro J. Jul 22 '13 at 19:09
0

If this is an Azure database the identity field can increment when the database replicates.

Andrew Foster
  • 1