Unaccountable jump in Identity column value

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

Re: Unaccountable jump in Identity column value

(teddysnips@hotmail.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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Unaccountable jump in Identity column value

On Aug 17, 11:16 am, Erland Sommarskog wrote:
> (teddysn...@hotmail.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.

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!

Will the weirdness never end?

Thanks

Edward

Re: Unaccountable jump in Identity column value

On Aug 17, 4:18 am, teddysn...@hotmail.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.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

Re: Unaccountable jump in Identity column value

On Aug 17, 2:50 pm, Alex Kuznetsov wrote:
> On Aug 17, 4:18 am, teddysn...@hotmail.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

Re: Unaccountable jump in Identity column value

(teddysnips@hotmail.com) writes:
> 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!

Obviously someone has inserted (attempt to insert) a lot of data, presumably
not through a GUI but through some SQL manipulation.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Unaccountable jump in Identity column value

Well, maybe someone did a large insert (of thousands of rows) and
cancelled the insert during operation.

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

Gert-Jan


teddysnips@hotmail.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

Re: Unaccountable jump in Identity column value

On Aug 17, 11:54 am, teddysn...@hotmail.com wrote:
> On Aug 17, 2:50 pm, Alex Kuznetsov wrote:
>
>
>
> > On Aug 17, 4:18 am, teddysn...@hotmail.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

In addition to Erland's and Gert-Jan's replies, DBCC CHECKIDENT may
reset current identity value which can cause a gap.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

Re: Unaccountable jump in Identity column value

On Aug 18, 2:37 pm, Gert-Jan Strik
wrote:
> Well, maybe someone did a large insert (of thousands of rows) and
> cancelled the insert during operation.

Under normal operation of the system this is completely impossible. I
cannot, however, guarantee that this was not done maliciously.

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

The presence of a gap is not important - the purpose of the Identity
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