Good afternoon
I have read the internet buzz on this and I am a bit
confused as to why this matters in the context of a forum or even anywhere.
It was discussed in depth during the early part of this year,
(e.g. http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity
)
Whilst in my mind there is clearly a bug, ID values are only lost when the server is
shut down from the service manager rather than after a T-SQL SHUTDOWN statement, this pretty clearly says that it
is a bug, otherwise the behaviour would be the same, but it doesn’t really matter.
An Identity column based on a signed int (32 bit), which is
often the smallest data type that they
are set up on has a range of 0 – 2^31 or 0 – 2,147,483,648
So you will only lose the IDs (1,000 for a 32bit int) if the
database is shutdown without a T-SQL shutdown being emitted. Knowing this to be an issue it should only
affect server crashes and if you are having many of these then you have much more
serious problems.
IDs have never been without gaps, rolled back INSERTs for example, so nobody can have created a working system that
relied on an unbroken sequence.
On one project I created, part of the security audit looks
at missing values in the sequence, looking for people with high levels of
privilege who may have added and later removed rows. So this is a pain, but in this
particular environment, which was mostly batch processing, a bit of common
sense was applied and still is.
Do you get still get lost Identities if you do a SHUTDOWN from the Enterprise Manager Query window, rather
than just turn the server off?
Bye
Ian
Topic Options
Post Options
Thanks(0)
