SQL Server 2017 has a wonderful feature which fixes a very old issue which DBA and developers have been struggling for a while. It would be impossible to find DBA or Developer who has not faced an issue when their identity column value has jumped or incremented by 1000. It is a very common issue and today we will see the solution of this issue with the help of IDENTITY_CACHE introduced in the latest version of SQL Server.
First, let us understand the issue which we face when we keep IDENTITY_CACHE = ON for SQL Server. Please note that as this is a default value is ON, so if you do not change anything you will see the following behavior.
Case 1: IDENTITY_CACHE = ON — default
First, make sure that value of identity cache is set to on by running following command.
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON GO
Create a sample table where there is one column which is identity.
IF OBJECT_ID('dbo.Identity_Cache_Demo', 'U') IS NOT NULL DROP TABLE dbo.Identity_Cache_Demo; GO CREATE TABLE Identity_Cache_Demo (ID INT IDENTITY(1,1) PRIMARY KEY, Colors varchar(50)); GO
Now let us insert few rows into the table and right after that let us insert few rows into the table.
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Red'); INSERT INTO Identity_Cache_Demo (Colors) VALUES ('White'); GO
Next, let us check the values in the table by selecting the data.
SELECT * FROM Identity_Cache_Demo GO
You may notice that the value of the ID column is 1 and 2.
Now we will run the following command inside a transaction. Please note that we will just begin the transaction but we will not complete the transaction.
BEGIN TRANSACTION INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Brown'); INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Pink');
Now we reached to a very important stage. We need to create a crash for SQL Server. To recreate unexpected shutdown, go to a New Query Window and run following command.
-- Run only in new query window SHUTDOWN WITH NOWAIT -- New Window GO
Now connect to SQL Server once again by turning on the services.
If you run the select command once again, you will not see the data which we had initiated in the transaction because the transaction was never complete and the data was never committed. This is correct behavior.
SELECT * FROM Identity_Cache_Demo GO
Now let us insert a couple of more rows into the table.
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Blue'); INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Orange'); GO
Once again we will run the select command see what is the value of the ID column.
SELECT * FROM Identity_Cache_Demo GO
You will notice that there is a gap of 1000 value in the Identity. This is because SQL Server had generated and cached the next 1000 identity values for the table. SQL Server does this for the improve the performance of the INSERT statements which runs on the table. However, due to the unexpected crash scenario, it also loses the pre-generated identity value and that behavior is the responsible for the 1000 value jump in the identity.
If you want to overcome this issue, you have to follow the CASE 2 where we keep the identity cache value to OFF.
Case 2: IDENTITY_CACHE = OFF
In this scenario, we will run the following script to turn off the identity cache.
-- Case 2 IDENTITY_CACHE = OFF ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF GO
Once we do this, we will run exactly the same scenario we ran in case 1 once again. As I have explained all the steps in detail in Case 1, I will directly jump the scenario where we insert values after the unexpected shutdown.
Please repeat all the steps which you have followed in step 1 and after once again run the SELECT statement. Over here you will notice that this time, it does not jump or miss any identity value.
SELECT *
FROM Identity_Cache_Demo
GO[/sql
However, when you keep your identity cache value to OFF, your SQL Server takes a very little performance hit, which you can safely ignore if you do not see any CPU or Memory Issues.
This feature can be very important when you are dealing with replication with heterogeneous databases. If you are doing replication between multiple database platforms, I suggest that you take a look at SharePlex. With the help of SharePlex, you can achieve high availability, scalability and data integration through real-time database replication across on-premises, cloud or hybrid databases.
Reference: Pinal Dave (https://blog.sqlauthority.com)
20 Comments. Leave new
Excelent explanation, Pinal.
But, how do I know if de IDENTITY_CACHE is ON or OFF at this moment?
Thanks!
I should write another blog post for it.
Hi Pinal,
Can you write a blog on how to audit database growth size over period of time.
Or if you written already please point me to there, I could not find.
Default trace is the only place where SQL stores growth.
https://blog.sqlauthority.com/2015/02/03/sql-server-script-whenwho-did-auto-grow-for-the-database/
You need to device your own solution where you keep size data in some other tables on regular basis. Later generate report from those tables.
Any idea why it’s not working on the (localhost)ProjectsV13 SQL Express database?
Thank you for this article. I was really confused as to why the identity column had jumped.
Can I ask if it is safe to just turn off the identity cache in a live environment?
For example, if a live database table has an identity column that has jumped rows by 1000/10000 on a number of occasions due to unexpected restart/shutdowns and we just turn off the identity cache on sql server, will this cause any issues or will the identity column simply increment from the last as normal from there on?
Hi, I came across your article because we experienced the same problem, but it was with SQL 2016. There doesn’t seem to be any acknowledgement that this is a problem with 2016, but our SQL Server is definitely 2016 and we had the identity gap problem occur.
Nice article. Great explanation. Please keep writing.
Hi,
Nice explanation as always. Keep it up.
I will like to discuss a point, i have encountered jumps in a single table:
Last Id was 2713, next was 12713, so 1000 jump seen.
Later on Id was 12720, next was 22720, 10,000 jump seen.
If the problem occurs again then we might have a 1,00,000 jump?
It seems that cache is not 1000 identities. This can be a real big problem over time when int limit is reached for identity columns.
Need your expert opinion on how to tackle this case. We are using SQL 2017 with replication.
What happens when Identity limit is reached, what are next steps for it?
Regards,
Muhammad Usman
you would get error if range in completed. Follow the workarounds which I have suggested.
spent long time to find the solution……….
Is setting IDENTITY_CACHE = OFF the same as turning on TF 272 in SQL 2016, in other words reverting to the SQL 2008 R2 behavior with individual allocation of identity values?
Hi Pinal , Can you share blog for how do I know if de IDENTITY_CACHE is ON or OFF at this moment?
How do I fix the 1000 Id jump in SQL 2012?
Well, thats a new feature in 2017, but we are working in old environment (say SQL2008). Can ypu please tell how to overcome the same problem in older versions? thanks.
How can I do this in SQL Server 2012?
Thanks, Pinal. Nice explanation. For others to check the configured IDENTITY_CACHE value, we can use sys.database_scoped_configurations system table.
SELECT * FROM sys.database_scoped_configurations
WHERE NAME = ‘IDENTITY_CACHE’
Dude, you have an article for everything. Just ran into this bug for the first time in 15 years (as far as I know), and sure enough, my man P.D. has a post about it. Thank you!
I just ran into this and didn’t know why. It’s a superb explanation. All this is great, but it left me hanging because I read also that having IDENTITY_CACHE off can lead to performance issues with inserts.
So if you don’t want to turn IDENTITY_CACHE off, how do you fix the problem and get things back to inserting on the next number in line so you don’t have the 1000 records skipped? I wasn’t sure why it wasn’t mentioned since it has lost the identity seed value in it’s cache, you can just reset the seed. My database identity seed was at 23. Next insert was 7028 or something. Ran DBCC CHECKIDENT(DBName, RESEED, 23). The next insert was at 24 as expected.
Superb explanation on the root cause though. Had no idea. Thanks for all of your info. You have saved me many times when I had no clue what was going on.
This post saved me a lot of work today. Thank you very much for the clear and detailed explanation Sir.