Posted by Alex Kuznetsov on 08/18/07 18:54

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

[Back to original message]