question

JoseArturoElizondo-6659 avatar image
0 Votes"
JoseArturoElizondo-6659 asked TomPhillips-1744 edited

problem with auto increment jump 1000

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Is there any update on this case?
If the answer is helpful, please click "Accept Answer" and upvote it.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoseArturoElizondo-6659 avatar image
0 Votes"
JoseArturoElizondo-6659 answered JeffreyWilliams-3310 edited

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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 *

0 Votes 0 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft edited

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoseArturoElizondo-6659 avatar image
0 Votes"
JoseArturoElizondo-6659 answered ErlandSommarskog commented

Hello, Thanks all for the request. About your question Criszhan. I add the -T272 3 days ago, but is not working.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Did you restart SQL Server after the change?

And more importantly, why do you care in the first place?

0 Votes 0 ·
JoseArturoElizondo-6659 avatar image
0 Votes"
JoseArturoElizondo-6659 answered ErlandSommarskog commented

Hi ErlandSommarskog, Yes I restart. I put this "-T272", now I change "-t272" i think is working. so T to t

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The option is definitely -T, not -t.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.