Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ Large Gaps In Identity Values After Each Server Restart

Posted by: FrankRuperto Jan 20 2020, 08:18 AM

Our Access/PostgreSQL guru stated in https://www.UtterAccess.com/forum/index.php?showtopic=2056560&hl= in which I quoted him:

QUOTE
If it's the large gaps in IDENTITY values, it's always been there but starting in SQL Server 2012 the value now jumps by 1000 after a server restart. Microsoft claims it is a feature.


If this is true and users shutdown their systems on a daily basis, there's going to be a bunch of gaps iconfused.gif

Posted by: GroverParkGeorge Jan 20 2020, 08:24 AM

If you use Identity fields properly, it should not matter.

Identity, like AutoNumbers in Access, is NOT intended to always return consecutive, unbroken, sequences of values.

I would ask for the link to the MS documentation, though, out of curiosity.

Posted by: FrankRuperto Jan 20 2020, 08:41 AM

The following links confirm this occurrence.

https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-SQL-server-database

https://web.archive.org/web/20160822054721/https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Posted by: GroverParkGeorge Jan 20 2020, 08:44 AM

Thanks for the references.

It is clear from reading them that this is a non-issue in the great majority of situations.

QUOTE
You should be aware neither of these workarounds assure no gaps. This has never been guaranteed by IDENTITY as it would only be possible by serializing inserts to the table. If you need a gapless column you will need to use a different solution than either IDENTITY or SEQUENCE


There are other work-arounds noted in the SO discussion.

Posted by: GroverParkGeorge Jan 20 2020, 10:05 AM

Also. If you are stopping and starting a SQL Server daily, which seems a bit unusual to me, you may or may not be impacted by the loss of potential values in the identity field depending on the volume of records being added to those tables. If that is of concern, you can implement one of the workarounds discussed.

Posted by: FrankRuperto Jan 20 2020, 10:34 AM

It's not unusual to stop any server db on a daily basis when you are shutting down a Windows workstation or server located in a business that closes its doors at the end of each workday. I even find it beneficial to periodically restart Windows on a daily basis because I have noticed systems become stale and slower when Windows is left running after an extended amount of time. I also encourage my users to get into the habit of closing their Access apps when not being used and unplug the internet unless we need to remote in for maintenance/support reasons.

UPDATE: I am not aware of any other server db's that create large gaps when they're restarted. Other server db's store the max committed identity column for each table in their schema's system catalog table and use these values to generate the next available identity number without needing to cache these values to improve performance.

Posted by: GroverParkGeorge Jan 20 2020, 10:40 AM

In that case, you need to investigate and, if necessary, implement one of the workarounds proposed in the article to which you link.


Posted by: PhilS Jan 20 2020, 06:19 PM

QUOTE
It's not unusual to stop any server db on a daily basis when you are shutting down a Windows workstation or server located in a business that closes its doors at the end of each workday.

Well, if you see a benefit in that, then just do it.
The Identity gap caused by the restart is going to cause a problem in ~5900 years. I wouldn't worry about that. - If you do, turn off the identity cache.

BTW: The statement of your "Access/PostgreSQL guru" contains several inaccuracies regarding Access and Microsoft SQL Server.

Posted by: FrankRuperto Jan 20 2020, 07:33 PM

PhilS,

QUOTE
The Identity gap caused by the restart is going to cause a problem in ~5900 years. I wouldn't worry about that. - If you do, turn off the identity cache.


I think the time estimate would also depend on how many rows are being inserted. There are SQL-Server db's where millions of rows are inserted on a regular basis, e.g. big data warehouse applications. I have just never come across a db server with this peculiar "MS feature".

QUOTE
The statement of your "Access/PostgreSQL guru" contains several inaccuracies regarding Access and Microsoft SQL Server.


Which statement(s)?... Perhaps you should point out the inaccuracies in that topic in which I requested feedback.

Posted by: MadPiet Jan 20 2020, 08:11 PM

What happens if you use a SEQUENCE object for that?

Posted by: FrankRuperto Jan 20 2020, 08:22 PM

On SQL-Server? I personally don't know because the few times I have tinkered with SQL-Server was with 2008 R2 SP3 and sequences didn't exist unitl 2012 version. IDENTITY was the only thing available in 2008 that guaranteed a unique automatic sequence number.

If using an Access autonumber for something other than a hidden RecordID for joins is frowned upon, how else can you guarantee a correct sequence number gets assigned in a large multiuser application for say something like a new Contract or Invoice number? Can a public function guarantee there wont be collisions, or the wrong sequence number gets assigned to the wrong insert, or the code wont break even if the LongInt field has a unique index?

Posted by: MadPiet Jan 20 2020, 09:48 PM

The only guarantee of an Autonumber is that it's unique, as far as I know.

Check out this thread:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_windows8-mso_2016/autonumber-field-changes/677f4f90-feaf-45b2-bc5e-08fec9c3fc08

Posted by: AlbertKallal Jan 21 2020, 01:16 AM

QUOTE
starting in SQL Server 2012 the value now jumps by 1000 after a server restart.


Actually, it claims it is a cache - and it "can" do this, not that it always does.

I running 2017, and I have never seen this occur. I regular re-start some of these servers, and also regular (near daily) re-start my server and I not seen this occur.

I wondering what the specific setup, and circumstances are that causes this to occur?
(I wonder if it is some kind of forced or un-expected restart?).

I actually never seen this. Mind you I don't look that close at auto numbers, but I not seen this this occur.

I wonder if this is due to replication being active? I suspect some other issue is occurring here (such as some kind of reboot or something?).

It seems that this is suggested that it can occur - but I not seen this occur as a regular event.

Interesting..... But I suspect some service, or some other feature is being used. I wonder if this has to do with the in-memory features being offered for databases?
(you can use a with memory modifier - this will force tables into memory - this can be of use for high transaction or high used lookup types of tables

So, while this can occur - its been since 2012, and this is the first time I have heard of this occurring, or that it is some new feature!
2012 is about 5 years old, I would have likely seen a LOT more posts about this issue if it was common occurring.

It should not be occurring as a normal re-boot, or even a re-start of SQL server.

Edit - you can disable this feature with

CODE
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF;


The above is for 2017, I don't know if the feature existed in 2015, but one can turn off this "supposed" feature.


R
Albert

Posted by: FrankRuperto Jan 21 2020, 07:58 AM

Hi Albert,

The answer in this SO post seems to shed light on why and when it skips numbers. I find this behavior to be odd, but as long as it guarantees unique ascending values then so be it, I guess iconfused.gif

https://dba.stackexchange.com/questions/1635/why-are-denali-sequences-supposed-to-perform-better-than-identity-columns

Posted by: GroverParkGeorge Jan 21 2020, 08:30 AM

Tempest in a teapot.

As Albert points out, this can occur in some specific situations, but is actually quite uncommon. And, of course, it can be avoided.

I suspect, further, that a large application where tens or hundreds of thousands of records are being added daily is probably NOT being restarted every day. A small organization adding hundreds of records daily may be prone to restarting more frequently, I suppose, if all computers--including servers--are shut down at 6:00PM when everyone goes home for the day. Statistically speaking, therefore, the risk of running out of values is relatively small.

"...long as it guarantees unique ascending values then so be it, I guess "

As has been pointed out over and over and over, there IS no such guarantee for AutoNumbers in Access nor for Identities in SQL Server.

There are alternatives such as SEQUENCE in newer versions of SQL Server and VBA functions In Access to create sequential values. If you need them, use an appropriate method.

Posted by: MadPiet Jan 29 2020, 02:32 PM

QUOTE
It's not unusual to stop any server db on a daily basis when you are shutting down a Windows workstation or server located in a business that closes its doors at the end of each workday.


Restarting a Windows Server (not a non-Server OS) on a daily basis sounds super sketchy. They're designed to run 24/7 for long periods of time. If you restart a server machine, you lose all the cached query plans and data. That's a great way to absolutely kill performance.

Posted by: FrankRuperto Jan 29 2020, 09:48 PM

QUOTE
If you restart a server machine, you lose all the cached query plans and data. That's a great way to absolutely kill performance.


On the other hand, restarting clears up temp files, logs, buffers, etc. and I am almost certain most organizations running on Server OS platforms are also using server db backends that provide things like in-memory tables, stored procedures and other features that optimize performance. Throughout time, I have noticed server and non-server Windows platforms becoming slower and stale after several days uptime, and most of the times, rebooting fixes those issues.

Posted by: MadPiet Jan 29 2020, 10:48 PM

If restarting fixes those issues, then whoever wrote the stored procedures etc probably doesn't know how to write good code.

If you want fun, post that question on StackOverflow and see if you can get Brent Ozar to answer.