UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Large Gaps In Identity Values After Each Server Restart, SQL Server 2012    
 
   
FrankRuperto
post Jan 20 2020, 08:18 AM
Post#1



Posts: 791
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Our Access/PostgreSQL guru stated in this other UA post 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

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 08:24 AM
Post#2


UA Admin
Posts: 36,970
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 08:41 AM
Post#3



Posts: 791
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


The following links confirm this occurrence.

https://stackoverflow.com/questions/1414614...server-database

https://web.archive.org/web/20160822054721/...eed-of-identity

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 08:44 AM
Post#4


UA Admin
Posts: 36,970
Joined: 20-June 02
From: Newcastle, WA


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.
This post has been edited by GroverParkGeorge: Jan 20 2020, 08:46 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 10:05 AM
Post#5


UA Admin
Posts: 36,970
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 10:34 AM
Post#6



Posts: 791
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


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.
This post has been edited by FrankRuperto: Jan 20 2020, 10:50 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 10:40 AM
Post#7


UA Admin
Posts: 36,970
Joined: 20-June 02
From: Newcastle, WA


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


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
PhilS
post Jan 20 2020, 06:19 PM
Post#8



Posts: 690
Joined: 26-May 15
From: The middle of Germany


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.

--------------------
A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 07:33 PM
Post#9



Posts: 791
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


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.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
MadPiet
post Jan 20 2020, 08:11 PM
Post#10



Posts: 3,552
Joined: 27-February 09



What happens if you use a SEQUENCE object for that?
Go to the top of the page
 
FrankRuperto
post Jan 20 2020, 08:22 PM
Post#11



Posts: 791
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


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?
This post has been edited by FrankRuperto: Jan 20 2020, 09:02 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
MadPiet
post Jan 20 2020, 09:48 PM
Post#12



Posts: 3,552
Joined: 27-February 09



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/msoffic...5e-08fec9c3fc08
Go to the top of the page
 
AlbertKallal
post Jan 21 2020, 01:16 AM
Post#13


UtterAccess VIP
Posts: 2,970
Joined: 12-April 07
From: Edmonton, Alberta Canada


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
Go to the top of the page
 
FrankRuperto
post Jan 21 2020, 07:58 AM
Post#14



Posts: 791
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


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/163...dentity-columns

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
GroverParkGeorge
post Jan 21 2020, 08:30 AM
Post#15


UA Admin
Posts: 36,970
Joined: 20-June 02
From: Newcastle, WA


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.
This post has been edited by GroverParkGeorge: Jan 21 2020, 08:46 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Jan 29 2020, 02:32 PM
Post#16



Posts: 3,552
Joined: 27-February 09



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.
Go to the top of the page
 
FrankRuperto
post Jan 29 2020, 09:48 PM
Post#17



Posts: 791
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


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.
This post has been edited by FrankRuperto: Jan 29 2020, 09:52 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
MadPiet
post Jan 29 2020, 10:48 PM
Post#18



Posts: 3,552
Joined: 27-February 09



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.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    31st March 2020 - 05:45 PM