Solved

Primary Key/Identity problems

Posted on 1998-07-14
4
266 Views
Last Modified: 2010-03-19
My SQL server keeps having the same problem. There are several tables that have a field that is an int, an identity, and is also the primary key of the table. Periodically, and for no apparent reason, the identity value will drop back, sometimes one or two numbers, sometimes 100 or more. Either way, since this field is a Primary Key, I can no longer insert any data into the table as the identity field is trying to number itself the same as a record that already exists. I've found the only solution for me so far is to remove the PK constraint, insert dummy data into the table until the index reaches a unique value, delete the dummy data, and then recreate the PK constraint.

Any idea what might be happening to cause this? Does anyone have a utility to correct the problem periodically, or is there a way to find the next identity value of a table without actually inserting, so that I can write my own utility to check the tables periodically?

Thanks,
\/\/ill R.
0
Comment
Question by:wrussell051197
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Accepted Solution

by:
bhkeng earned 70 total points
ID: 1091753
What version of SQL Server are you using? Apparently there is an undocumented identity column bug in the early version of SQL Server 6.0. It causes intermittent corruption to identity columns in tables. Microsoft has fixed this bug apparently in SQL Server 6.0 Service Pack 3.

Solution to correct the problem is execute:

DBCC checkident (table_name)

which will automatically correct the identity column, after which i recommend you upgrade to latest service pack for SQL Server. Ask the DBA to run this built in checking utility regularly.

Good luck.
0
 

Author Comment

by:wrussell051197
ID: 1091754
I'm running SQL server 6.5, and I installed the latest service pack about 2 weeks ago hoping that would correct the problem. It didn't I'll try the checkident and see if that helps. BTW, I don't see DBCC checkident documented in the Transact-SQL Reference, is there a reason Microsoft left out that part of DBCC?
0
 

Author Comment

by:wrussell051197
ID: 1091755
Ok, that seems to works. I'll go ahead and just stick that into the weekly maintenance plan, and see if that cuts down on my problems.
0
 

Expert Comment

by:bhkeng
ID: 1091756
I am not sure why checkident was left out... it is an undocumented feature.. took some digging before we found out about it.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question