Solved

Primary Key/Identity problems

Posted on 1998-07-14
4
243 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
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 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

16 Experts available now in Live!

Get 1:1 Help Now