Reaching the Outer Limits

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

  • BobAtDBS

    SSCarpal Tunnel

    Points: 4334

    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

  • Mark Harr

    SSCrazy Eights

    Points: 9760

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

    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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • vpolotsky

    Grasshopper

    Points: 14

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

  • Ojamie

    Valued Member

    Points: 55

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

    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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • Kashif

    SSCommitted

    Points: 1881

    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

  • Simon Sabin

    SSCrazy Eights

    Points: 8142

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

    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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • David.Poole

    SSC Guru

    Points: 75083

    All hail the GUID, thats what I say!

  • PeterLivesey

    SSC Veteran

    Points: 204

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

    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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • Richard L. Dawson

    Ten Centuries

    Points: 1061

    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 19 total)

You must be logged in to reply to this topic. Login to reply