Use of shutdown with nowait can lead to gaps in IDENTITY column values..?

[Former Member]

Use of shutdown with nowait can lead to gaps in IDENTITY column values..?

can any one explain

[jayrijnbergen · Accepted solution]

identity values are reserved per block and kept in memory for performance reasons

when there's a shutdown with nowait, the reserved block in memory is discarded

after the ASE is restarted, a new block is reserved

to control the block size which is reserved in memory, you can configure:

identity burning set factor

[former_member89972]

Identity gaps need to be fixed eventually.

Look for following on Rob Verschoor's website.

He also has full chapter in his book on this topic.

Book: "Tips, Tricks & Recipes for Sybase ASE"

Tips & Tricks

     Identity gaps - CIS&XP - UDFs - Dynamic SQL: (1), (2) - Grant SA rights - sqsh tricks -

     sproc-in-table

Sybase Tips, Tricks, Tools & Documentation by Rob Verschoor

HTH

Avinash

[jayrijnbergen]

Careful with that, the info on Rob's website applies to old versions (pre-15)

to fix identity gap in recent versions, use these set options:

set identity_update <tablename> on

set identity_insert <tablename> on

after running the set command, you can update existing identity values and insert new values

the identity_update / identity_insert commands require table owner/dbo access

[former_member188958]

"Identity gaps need to be fixed eventually".

No, they don't.   As long as you are using them for their original

design purpose - quickly generated values that are guaranteed to be unique  -

then gaps don't need to be "fixed" because the values generated continue

to be unique.

-bret

[Former Member]

This explains it - see "Brief summary of identity gap issues" in

Identity gaps in Sybase ASE

Basically you need to set the "identity burning set factor" to a low-ish value ... but also worth looking at "identity grab size"

see

SyBooks Online

and

SyBooks Online