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
Solved

Primary Key/Identity problems

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

856 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