October 5, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/reachingtheouterlimits.asp
October 14, 2002 at 6:51 am
Good article, Steve. When I first read the error message, my guess was that you had 'run out' of numbers on the Identity column, it's happened to me before. I appreciate you detailing the steps you went through on finding the problem. Watching others troubleshoot is a powerful learning tool.
I disagree with one point, though. I don't see it as a reason to not use the Identity column, I see it as a reason to plan for maintenance. Eventually (if your code survives long enough!) you are going to run out of whatever you use for a unique ID. The trick is to place that event a long ways from now, and have some way of monitoring for when it's going to happen.
Incidently, does this mean you've had 2 billion viruses? Hope not!
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
October 14, 2002 at 6:52 am
I was with a group of developers several years ago, that determined through some sort of testing that when SQL Server recovers from a catastrophic or abnormal shutdown, it bumps up the next identity value by factors of 10 or more. That is, the first time it fails, the next id will be in the 1,000,000 range; after the second recovery it will 10,000,000 range, etc. Because of this, we made it our standard to only use Identity fields for transaction type tables (where the table is regularly truncated and the identity value is reset).
Mark
October 14, 2002 at 10:13 am
I think it can be a reason not to use the identity. What if you were importing 3B rows a day? Or a week, it might be a reason to not use it. You have to be aware and not let it creep up on you.
No viruses, this is the reporting tool and it "checks" in with the server on a schedule to send information. Appears that McAfee has some type of bug as they are maintaining around 1M rows in out table, but they are "creeping" up, so today they are 34-35M, tomorrow they might be 35-36M. Strange.
For mharr, I've had quite a few abnormal shutdowns and crashes. No issues with identity here. There were some problems with v6.5 and transactions, but haven't seen any in 7 or 2000. Don't worry about that now.
Steve Jones
October 14, 2002 at 1:38 pm
Great article. Was this table frequently truncated and then reloaded ("full load") without resetting the identity? In each such a load the identity obviously starts increasing from the "previous incarnation" (before truncation) largest number. It's still hard to get to such a large number (load 10 million rows 300 times?,) but not impossible
October 14, 2002 at 2:47 pm
Not sure. We are still researching this with McAfee.
Steve Jones
October 14, 2002 at 3:19 pm
that is a pretty obvious error to debug, isn't it?
here is a simple example
create table test(i tinyint identity, j int)
insert test select id from master..sysobjects
October 15, 2002 at 10:02 am
It may be for you, but it threw me briefly, especially when the table involved was for a relatively new server.
What is simple for one person may not be for another. Especially if you have never seen it.
Steve Jones
October 16, 2002 at 12:28 am
Few month back one of our team member reported this error to me. But he was just stress testing the identity columns which are used in the database. Not in a production database. This article certainly makes me more careful when using IDENTITY columns as primary keys.
vpolotsky: Truncation always resets IDENTITY column value to its seed. See 'TRUNCATE TABLE' in BOL
October 16, 2002 at 8:00 am
Is replication involved, if they are using identity ranges, could something of gone screwy.
I am sure you are aware as well of bigint which is an 8byte integer. allowing some obscene number to be stored.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 16, 2002 at 9:35 am
No replication. Not sure why the values are creeping up, but pinged McAfee on the issue.
I suggested BIGINT, but since it's a 3rd party db and software, hestitant to do that. Never know if the client can handle an int > 32 bit.
Steve Jones
October 17, 2002 at 1:57 am
All hail the GUID, thats what I say!
January 20, 2003 at 7:18 am
I have not come accross this exact issue but I have come accross a similar issue involving the timestamp field.
When coding an ASP/COM site with an SQL Server back end I decided to use the timestamp field as a dirty data check. Because you are dealing with disconnected recordsets and multiple users it is a good idea to check that no-one has updated data before you update as you will overwrite there changes. For this I wrote a COM interface that checked a copy of a timestamp field against one that had been sent to the client, if it hadn't changed then I allowed the update if not then I sent back an error code to the client.
This worked great, but about six months into production the busiest table was reporting an error when trying to update.
The error was that when calling the COM method the timestamp field was placed in the CINT(Method) and had become too large for ASP to convert into an integer.
The fix was very simple
either change to CLNG() or leave as a variant, however this reinforces the need to do as much load testing as possible on a database.
Mr Peter Livesey
Mr Peter Livesey
January 27, 2003 at 11:41 am
Interesting. Haven't used the timestamp column since some Visual Fox work 6 years ago, but that was how Fox determined if you could update the RS as well. Hope the ADO people have this fixed for .NET.
Steve Jones
October 14, 2003 at 2:52 pm
Hi Steve,
Great article. Especially the steps you went through for tracking down the true reason for the error. I have always wished for more 'How To' (with a whole lot more 'WHY To') type articles since I started working with Sql Server.
I do disagree with your blanket statement about using IDENTITY columns. It's like anything else in the computer world. You have to understand your requirements and act accordingly. For the vast majority of us IDENTITY leaves plenty of room for tables to grow. In most cases we don't have the hardware to support tables with 2+ billion rows
😉
Finally, anything else as a primary key just slows things down.
Richard L. Dawson
Database Admin/Developer
ICQ# 867490
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply