 | |
Unaccountable jump in Identity column value
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Unaccountable jump in Identity column value -
08-17-2007
, 04:18 AM
#2
| |||
| |||
|
|
In APP 1 (Access front end, SQL Server 2000 backend) there is an operation to generate a discrepancy report. These are numbered sequentially, and this uses an Identity column with a seed and increment of 1. These reports are added approximately once a week. This week the value of the Identity column has jumped from 5,399 to 8,420. I need hardly add that no-one has added just over 3,000 reports in the interim. Can this be related to the bizarre behaviour as reported in the other thread? |
fails. Say for instance that the INSERT was part of a user-defined
transaction what then was rolled back because of a timeout, and then was
reattempted and reattempted.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#3
| |||
| |||
|
|
(teddysn... (AT) hotmail (DOT) com) writes: In APP 1 (Access front end, SQL Server 2000 backend) there is an operation to generate a discrepancy report. These are numbered sequentially, and this uses an Identity column with a seed and increment of 1. These reports are added approximately once a week. This week the value of the Identity column has jumped from 5,399 to 8,420. I need hardly add that no-one has added just over 3,000 reports in the interim. Can this be related to the bizarre behaviour as reported in the other thread? Maybe. Keep in mind that IDENTITY values are consumed even if an INSERT fails. Say for instance that the INSERT was part of a user-defined transaction what then was rolled back because of a timeout, and then was reattempted and reattempted. |
initiated, especially the generation of this particular type of
record. Further, there is currently only one user of the system
(though it is, believe it or not, mission critical, and this is a
major aerospace company!) and he would not hit the "Add New Record"
button 3,000 times. If it failed the first time, he'd call me!
Just to keep this up-do-date, I have heard from the sysadmin that
there are other databases (which my company does not support) on the
same database server where the data has been quadruplicated!
Will the weirdness never end?
Thanks
Edward
#4
| |||
| |||
|
|
And here's another thing that's gives me pause for thought (for other information see the thread entitled "ODBC Timeout problems but very hard to pin down") In APP 1 (Access front end, SQL Server 2000 backend) there is an operation to generate a discrepancy report. These are numbered sequentially, and this uses an Identity column with a seed and increment of 1. These reports are added approximately once a week. This week the value of the Identity column has jumped from 5,399 to 8,420. I need hardly add that no-one has added just over 3,000 reports in the interim. Can this be related to the bizarre behaviour as reported in the other thread? Thanks Edward |
You could use profiler to trace what's going on.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/
#5
| |||
| |||
|
|
On Aug 17, 4:18 am, teddysn... (AT) hotmail (DOT) com wrote: And here's another thing that's gives me pause for thought (for other information see the thread entitled "ODBC Timeout problems but very hard to pin down") In APP 1 (Access front end, SQL Server 2000 backend) there is an operation to generate a discrepancy report. These are numbered sequentially, and this uses an Identity column with a seed and increment of 1. These reports are added approximately once a week. This week the value of the Identity column has jumped from 5,399 to 8,420. I need hardly add that no-one has added just over 3,000 reports in the interim. Can this be related to the bizarre behaviour as reported in the other thread? Thanks Edward Edward, You could use profiler to trace what's going on. |
add records, but there's a gap of 3,000 missing records. Fortunately,
it doesn't matter - the number is simply used to identify the record,
and it is guaranteed (by virtue of being an Identity column) to be
unique. I just can't imagine where the other 3,000 rows have gone.
Well, I know there *aren't* 3,000 missing rows - there are no missing
rows, just a gap in the numbering.
Edward
#6
| |||
| |||
|
|
I take your point. However, all transactions on this system are user- initiated, especially the generation of this particular type of record. Further, there is currently only one user of the system (though it is, believe it or not, mission critical, and this is a major aerospace company!) and he would not hit the "Add New Record" button 3,000 times. If it failed the first time, he'd call me! Just to keep this up-do-date, I have heard from the sysadmin that there are other databases (which my company does not support) on the same database server where the data has been quadruplicated! |
not through a GUI but through some SQL manipulation.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#7
| |||
| |||
|
|
And here's another thing that's gives me pause for thought (for other information see the thread entitled "ODBC Timeout problems but very hard to pin down") In APP 1 (Access front end, SQL Server 2000 backend) there is an operation to generate a discrepancy report. These are numbered sequentially, and this uses an Identity column with a seed and increment of 1. These reports are added approximately once a week. This week the value of the Identity column has jumped from 5,399 to 8,420. I need hardly add that no-one has added just over 3,000 reports in the interim. Can this be related to the bizarre behaviour as reported in the other thread? Thanks Edward |
#8
| |||
| |||
|
|
On Aug 17, 2:50 pm, Alex Kuznetsov <AK_TIREDOFS... (AT) hotmail (DOT) COM> wrote: On Aug 17, 4:18 am, teddysn... (AT) hotmail (DOT) com wrote: And here's another thing that's gives me pause for thought (for other information see the thread entitled "ODBC Timeout problems but very hard to pin down") In APP 1 (Access front end, SQL Server 2000 backend) there is an operation to generate a discrepancy report. These are numbered sequentially, and this uses an Identity column with a seed and increment of 1. These reports are added approximately once a week. This week the value of the Identity column has jumped from 5,399 to 8,420. I need hardly add that no-one has added just over 3,000 reports in the interim. Can this be related to the bizarre behaviour as reported in the other thread? Thanks Edward Edward, You could use profiler to trace what's going on. Thanks, Alex. Unfortunately it's already gone on. The user can now add records, but there's a gap of 3,000 missing records. Fortunately, it doesn't matter - the number is simply used to identify the record, and it is guaranteed (by virtue of being an Identity column) to be unique. I just can't imagine where the other 3,000 rows have gone. Well, I know there *aren't* 3,000 missing rows - there are no missing rows, just a gap in the numbering. Edward |
reset current identity value which can cause a gap.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/
#9
| |||
| |||
|
|
Well, maybe someone did a large insert (of thousands of rows) and cancelled the insert during operation. |
cannot, however, guarantee that this was not done maliciously.
Quote:
|
Anyway, IMO an Identity column is a very poor idea if you really need a sequence without gaps. Identity columns are simply not meant to do that. If that is what you want, then I would remove the Identity property and determine the sequence number myself (using a reliable method). If the Identity column is simply used to generate a unique meaningless surrogate key, then I wouldn't worry about gaps, and would attempt to analyze it either... |
column is purely for purposes of providing a unique number for the
record. However, the presence of the gap IS important because there
are other problems with databases on this server and makes me think
that there's something seriously amiss.
Edward
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.