0

I've got a .NET application which has been running for about 10 years using SQL server as a backend. For some reason, over the last 2-3 months, it has decided to skip out around 990 (I think it has been 997 twice and 989 once) IDENTITY numbers 3 times. I can't think of any activities that the system does that would generate records and then delete them or attempt to insert that many times. This is a very intermittent problem, but it is very odd and I'd like to get to the bottom of it. Does anyone have any ideas for how to go about finding the cause?

The system is a mixture of different technologies - the older stuff is ADO.NET and the newer is Linq to SQL. I don't think it is feasible to log every statement executed or anything as it is quite an active system with a number of users.

wizzardmr42
  • 1,634
  • 12
  • 22
  • Seen that, but I'm not worried about odd skips here and there. I know what kind of things can cause identities to be skipped, but I don't think anything *should* be being run that does and it never has done in the past so I would like ideas for how to track down the query that is causing it. – wizzardmr42 Apr 18 '13 at 18:03
  • There may not be a query that causes it. Gaps can happen for purely internal reasons (e.g. the documentation [mentions](http://msdn.microsoft.com/en-us/library/ms186775.aspx) caching as one reason) so there is nothing that you can 'fix'. You could play around with SQL Profiler, but your real problem seems to be that users are treating meaningless numbers as if they do have meaning, so you need to focus on that issue. Perhaps you can improve the application GUI to reduce errors in data entry, or perhaps they really need a sequential (or meaningful) ID but have never asked for one clearly. – Pondlife Apr 18 '13 at 18:35
  • I have difficulty believing that it is something like a caching issue - I can't believe that it is caching 1000-odd IDs on the scale of a table that has generated about 30000 records in 10 years and then scrapping them unless something screwy is going on with the caching algorithm. The problem isn't data entry within the application - it is more when they use these IDs as refs in documents and emails. There is no reason they have to rely on them being absolutely sequential, but after 10 years of them being, they have got into a habit of thinking that way – wizzardmr42 Apr 18 '13 at 21:25

1 Answers1

1

This is normal - if an attempt to update fails due to constraint violation (say because of an attempt to insert NULL into a non-nullable column, or a non-existent foreign key, for instance), the identity field will increment but the row will not insert.

The next inserted row (assuming it will not fail) will increment the identity and insert, leaving a "gap".

This is not a problem.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • I'm aware of that, but the users do use the identity field as a reference and it confuses them if it skips as they expect sequential numbering. I am also concerned that I have no idea what is attempting to do so many inserts at random intervals and I'd like to track it down – wizzardmr42 Apr 18 '13 at 11:16
  • @wizzardmr42 - Exposing internal Ids like that is not advisable, for this and other reasons. Anyway, what's the problem? It isn't as if they will see an invalid Id to use anywhere? Your application should account for that. – Oded Apr 18 '13 at 11:17
  • I'm aware about not exposing internal IDs, but it is a project that I took over about 3 or 4 years ago and it wasn't a priority to do anything about that. The problem is that they reference the IDs regularly eg. in documents and they are more likely to make mistakes when there is a sudden skip and mistype an ID which is eg. 1000 out from the correct one – wizzardmr42 Apr 18 '13 at 12:18
  • 1
    @wizzardmr42 - A typo is a typo. This would be a problem even if the Ids were sequential (they would be pointing to the _wrong_ record, instead of finding out that there is no such record). – Oded Apr 18 '13 at 13:20
  • Uh, no - the point is that if all the IDs they have been dealing with are eg. in the 42000 range then they expect to get into the 42900s before they hit the 43000 range. If they suddenly jump from 42100 to 43097 then there is a very high chance that they will misread eg. 43103 as 42103 because they aren't expecting the jump. I'm pretty sure they are a lot more careful to check the latter digits and telling them to be more careful isn't really the solution that I'm looking for. – wizzardmr42 Apr 18 '13 at 17:57
  • Quite apart from all that, it isn't identifying what it is that is trying to do so many inserts and I would really like to know in case there are any other problems related to it. If it were just the odd ID that has been skipped or I was aware of any batch inserts being run that could have rolled back, I wouldn't worry about it – wizzardmr42 Apr 18 '13 at 17:59