Solved

Primary Key/Identity problems

Posted on 1998-07-14
4
235 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
  • 2
  • 2
4 Comments
 

Accepted Solution

by:
bhkeng earned 70 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now