×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

TIP: Understanding why the identity field sometimes jumps massively (

TIP: Understanding why the identity field sometimes jumps massively (

TIP: Understanding why the identity field sometimes jumps massively (

(OP)
TIP: Understanding why the identity field sometimes jumps massively (I've seen 30 quadrillion)

Background: What is the identity burning set factor?

The identity burning set factor configuration parameter is a percentage in decimal form x 10 million.

The default value, 5000, represents .05%, or .0005.

When an identity column is defined in a table, the server automatically assigns a unique sequential number for that identity column during inserts. To avoid excessive I/O, Adaptive Server Enterprise keeps blocks of numbers available in memoryfor use in identity values and keeps track of the current pointer in that block in memory.

The number of values in the block is controlled by the identity burning set factor. The identity column is a numeric datatype with a pre-defined precision. The server uses the possible range of values to determine which numbers are reserved.

For example, an identity column, "colA," is defined as numeric(7,0). The values of this column range from 1 to 9,999,999. Using the default burning set factor of 5000, .05% of the values are made available at a time. Therefore, the size of a block of numbers reserved in memory is .0005 (or .05%) x (9,999,999 +1) = 5000 values.

If the server shuts down unexpectedly, all unused numbers in the current reserved block (in memory) are lost. When the server restarts, the next insert starts with the next block. Therefore, abnormal shutdowns can cause large gaps in the identity column. An orderly shutdown (normal) does not delete identity values.

Note:
A shutdown with nowait is an unexpected shutdown and
causes the block of numbers to be lost. For more
information, see the Adaptive Server Enterprise System
Administration Guide, the Transact-SQL User's Guide,
and the Adaptive Server Enterprise Reference Guide.


Example: How Adaptive Server "loses" block values

In this example, we use the above identity column, colA, and perform a shutdown with nowait:

1.After 5 inserts into the table we have:

colA
-------
1
2
3
4
5


2.We now shut down the server with nowait, then re-start the server.

select colA from table:

colA
-------
1
2
3
4
5

3.We perform an insert into the table, then select colA again:

colA
-------
1
2
3
4
5
5002

4.If we perform a shutdown with nowait again, then repeat the insert and select colA again, colA displays as
follows:

colA
-------
1
2
3
4
5
5002
10003


Note:
The starting value in the next block almost always will not
be the first value in the new block. This is because of a
current value that is stored in the dbtable info. When the
block is lost, the server goes to the next block and the next
value in the dbtable info. In the above case, the last value in
the dbtable was 1.


Correcting identity column gaps

You can fix the rows containing the large numbers to correct gaps in the identity column. Perform the following steps:

1.Turn on identity_insert.

1> set identity_insert table_name on

2> go

2.Record the row(s) information. Because you cannot update an identity column, you must delete the row(s) and insert them again with the correct identity values.

3.Correct all rows in error.

4.bcp out the data from the table.

5.Turn off identity_insert.

1> set identity_insert table_name off

2> go

6.Drop the table and re-create it. This step resets the identity column's auto-start number to 1.

7.bcp the data back into the table using the -E option to maintain the identity values that appear in the host file.

Note:
The -E option tells the server not to use the auto-insert
identity numbers and to use the identity numbers included
in the bcp data.


The next identity number should be one greater than the largest identity number in the table.


Understanding the identity grab size

The identity grab size parameter allows a server process to reserve a block of identity column values for inserts into tables with identity columns.

What this means is that a particular server process gets its own block of numbers to use. This is useful if a particular server process needs to insert contiguous identity values. Identity grab size is a server-wide parameter, so do not set it to a high value. Set identity grab size only to a value large enough to accommodate the largest group of records you want to insert into contiguous rows. The default is 1.

Troubleshooting the identity burning set factor

In this example, performing shutdown with nowait confuses the user into believing that the identity burning set factor did not re-set.

0800 hrs. Create table burn1(col1 char(4), col2 numeric identity). Inserted several rows. Select * from burn1 returns:

col1 col2
---- --------------------
aaaa 1
aaab 2
aaac 3
aaad 4

When the first insert is done, a block of 500000000000000 numbers is taken as the first block of numbers.

0815 hrs. shutdown with nowait.

With the nowait, the server is unable to write the current identity number back to disk, so the rest of the
500000000000000 block of numbers is lost.

0816 hrs. startserver

0817 hrs. insert 1 row into burn1. select * returns:

col1 col2
---- --------------------
aaaa 1
aaab 2
aaac 3
aaad 4
aaae 500000000000002

As expected, we lost the first block of 5000000000000 numbers. The insert of 1 row above causes the server to get the next block of 500000000000000 numbers.

0818 hrs. Change identity burning set factorfrom 5000 to 10.

1> sp_configure "identity burning set factor", 10
2> go

Configuration option changed. (return status = 0)

1> sp_configure
2> go

name minimum maximum config_
value run_value
---------------------------- ----------- ----------- ------------ ----
-------
...
...
identity burning set factor 1 9999999 10
5000

0820 hrs. shutdown with nowait again.

With the nowait, the server is unable to write the current identity number back to disk, so the rest of the second 500000000000000 block of numbers is lost.

0822 hrs. startserver

0823 hrs. Check that identity burning set factor has changed.

1> sp_configure
2> go

name minimum maximum config_
value run_value
---------------------------- ----------- -------
---- ------------ -----------
...
...
identity burning set factor 1 9999999 10
10

0825 hrs. insert 1 row into burn1. select * returns:

col1 col2
---- --------------------
aaaa 1
aaab 2
aaac 3
aaad 4
aaae 500000000000002
aaaf 1000000000000003

As expected, the server lost the second block of 5000000000000 numbers. The insert of 1 row above causes the server to get the next block of 1000000000000 numbers.

Note:
The above output from the select, which shows that the
server deleted the second large block of numbers, often
confuses users into believing that no change to the identity
parameter occurred.


0827 hrs. shutdown with nowait again.

0830 hrs. startserver

0825 hrs. insert 1 row into burn1. select * displays:

col1 col2
---- --------------------
aaaa 1
aaab 2
aaac 3
aaad 4
aaae 500000000000002
aaaf 1000000000000003
aaag 1001000000000004

As expected, the server lost the first block of 10000000000 numbers. The insert of 1 row above causes the server to get the next block of 1000000000000 numbers.

This last step verifies that the identity burning set factor change worked.

Happy Sybaseing, Brian

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close