January 7, 2005 at 7:58 am
Hello All,
We have a SQL Server 2000 table (approx 1.2 million rows) with an identity column (int, seed at 1, increment at 1, primary key). The table has been in production for 27 months. No changes to table schema have been made since it's creation.
For an unknown reason the identity value no longer is incremented by 1. Each new row now increments with a random value between 1 and 30, but always as a higher number. We have identified the specific row (about 150k rows ago) when this started but that hasn't helped resolve the problem.
We have removed and replaced the primary key designation on that column and changed the increment value to 2 but that made no difference. No other tables in this very active database display this problem. A search of Microsoft Knowledge Base didn't produce an answer for us.
I am not an experienced day-to-day DBA. The solution may be simple, but I'm not sure what to do at this point.
Any suggestions would be appreciated. Thanks
Don
January 7, 2005 at 8:36 am
The simplest answer to give is you have inserts that are failing. Run profiler for a while and see if you have any inserts that fail and rollback. If so that number will not be in the table afterwards and depending on the number of failures you get can produce large gaps.
January 7, 2005 at 11:42 am
Hello Antares686,
Thanks for your quick and valuable response. We didn't realize a failed insert could still increment an identity counter.
That was the clue we needed. Found the problem, an unexpected value being passed to the script, and fixed it. It is really much more involved but don't want to bore everyone. Anyway once discovered it was an easy fix.
Thanks again....
Don
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply