Manage Your Identity

Posted: October 19, 2010 in SQLServerPedia Syndication

A couple of weeks ago there was a company that had a long and very public outage. From the information that I was able to find it appears that the outage was caused by a limit that was set in the database maybe years ago when the database was first designed. It appears that the company was using an identity on an integer as one of the columns in one of the tables. All was working well until the identity reached the 2.1 billion mark (2,147,483,647). When the next row was being inserted the identity could not insert another value because the int just could not store the value. It was out of range. I would like to say this issue is an isolated case, but the truth is I have seen this in other companies as well. As we people/companies understand the importance of data we start keeping more of it.

I spent some time looking at this issue and I was looking at some of the ways that the outage can be avoided. After looking and thinking about this for a short bit I decided that I needed to monitor or manage the values of my Identity’s. By using a table that I have blogged about before I simply ran a query to find all the identity columns and the max values they had. I then insert into this table the percentage of the identity values used. The query looks something like this.

SELECT sch.name AS SchemaOwner,

    obj.name AS ObjectName,

    col.name AS ColName,

    typ.name AS Datatype,

    CASE typ.length

        WHEN 4 THEN 2147483647

        WHEN 8 THEN 9223372036854775808

        ELSE 32000

    END
AS Size,

    Last_value AS LastValue,

    CONVERT(INT,
CONVERT(INT,last_value)/CASE typ.length

                WHEN 4 THEN 2147483647

                WHEN 8 THEN 9223372036854775808

                ELSE 32000

                END

    * 100)

    AS PercentFull

INTO #IdentCheck

FROM
sys.identity_columns col –sys.all_columns col

    INNER
JOIN
sys.systypes typ


ON col.system_type_id = typ.xtype

    INNER
JOIN
sys.objects obj


ON col.object_id = obj.object_id

    INNER
JOIN
sys.schemas sch


ON obj.schema_id = sch.schema_id

WHERE col.is_identity = 1

    AND obj.type =
‘U’

ORDER
BY sch.name, obj.name

 

SELECT
*
FROM #Identcheck

WHERE PercentFull >= 1

 

 

INSERT
INTO DBStats..MyTable

    SELECT

    ‘Ident Check’,

    GETDATE
(),

    SCHEMAOwner +
‘.’
+ ObjectName +
‘.’
+ ColName +
‘ Is ‘
+
CONVERT(CHAR(3), PercentFull)
+
‘PercentFull’

    FROM #Identcheck

    WHERE PercentFull >= 1

Does this solve the issue? No. This will not allow me to store greater values, however what this does do for me is give me a heads up on what tables are starting to reach the point where the field value needs to be increased. This is designed to give me the advanced notice so that the outage does not creep up on me.

Comments
  1. Dave Levy says:

    Right there with you on this one. It is amazing that none of the monitoring packages currently watches for this.

    Here is another version that handles more data types: http://adventuresinsql.com/2010/10/how-do-i-spot-identity-columns-that-are-about-to-max-out/

  2. Chris Shaw says:

    That is the better one then the one posted here. Thanks Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s