[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Primary Key/Identity problems

Posted on 1998-07-14
4
Medium Priority
?
273 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 280 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

650 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