Hello, I see a problem with auto increment jump 1000.
I add this on MSSQL -T272 but not working.
I use the version 13.0.4 and SSMS is 2019
Hello, I see a problem with auto increment jump 1000.
I add this on MSSQL -T272 but not working.
I use the version 13.0.4 and SSMS is 2019
Hi,
Is there any update on this case?
If the answer is helpful, please click "Accept Answer" and upvote it.
Did you consider an alternative that does not require major changes: setting IDENTITY_CACHE to OFF according to various suggestions: https://www.bing.com/search?q=t-sql+IDENTITY_CACHE? I.e.:
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
Why would that be problem? If it is a problem, that is, you need consecutive numbers, you should not use the IDENTITY property at all. IDENTITY is designed for when you need to number something but you don't care about the numbers being consecutive. IDENTITY is designed to produce gaps to permits inserts with high concurrency, so that one process can insert a new row without having to wait for the previous guy to commit. Which means that if this previous guy rolls back, there will be a gap.
Also, to increase speed further, SQL Server does not write to disk every time an IDENTITY value is taken, but only for every 1000 rows (for an int column). This means that you can get larger gaps if SQL Server crashes and restarted.
But as I said, if you have used IDENTITY for the right thing, this is not a problem. If you are using for the wrong thing, well, you should reconsider your redesign.
All that said, you can do
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
But beware that this is a go-slower button.
I running the script and show me this error
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'IDENTITY_CACHE'.
Completion time: 2021-07-12T16:34:26.1927991-05:00
Seems like you are on SQL 2016. (The specification of your version was not very clear. There is no SSMS 2019, so I took that to be the SQL version.)
But that is really good news, because that means that you shoot yourself in the foot with this go-slower button, but you will have to live with it. Or change your design.
What is the compatibility level set to for that database? Never mind - you can't set IDENTITY_CACHE using the specified command in 2016, it was introduced in 2017 *
Hi,
You may already know that this is a change introduced since SQL Server 2012, the identity caching is used to improve INSERT performance on tables with identity columns.. If this is really a problem for you, but because your SQL Server version is 2016 (cannot set IDENTITY_CACHE = OFF), You may need to use sequence or add -t272 to SQL Server startup parameters.
Can you see the T272 trace flag in the startup parameters in SQL Server error log or registry?
Hello, Thanks all for the request. About your question Criszhan. I add the -T272 3 days ago, but is not working.
Did you restart SQL Server after the change?
And more importantly, why do you care in the first place?
Hi ErlandSommarskog, Yes I restart. I put this "-T272", now I change "-t272" i think is working. so T to t
This problem is almost entirely eliminated by using a current patch level. This only happens in the server is not shutdown properly.
Please make sure you have the current patches installed:
https://docs.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level
18 people are following this question.