Recently a client of mine had complained to me about an issue they were facing when restarts happen on a system. After a restart the system skips the identity value by a 10 – 1000 depending on the data type of the identity column. This was a known issue in SQL 2012 and was touted as a feature which annoyed a lot of people btw.
However a fix was provided for it later on and I figured that was the end of the story. However after checking it out myself I found that the issue still persists in SQL 14 as well. But in a slightly different way. Previously the issue in SQL 2012 used to happen even with a controlled shutdown of SQL Server using SSMS or service control manager. But in SQL 2014 it doesn’t happen that way with a planned or controlled shutdown. e.g. if I insert 10 rows into a table then execute the “shutdown” command and restart the service the next row will have an id of 11. However if I simulate a power failure (end task SQL server service from task manager) and then insert a row the id will be 1004 which indicates the issue still exists and is noticeable only after a unplanned shutdown. Which in most cases is a problem because we aren’t looking for this problem at that time.
So is there a solution now?
Well yes and no. SQL 14 even with the latest service pack doesn’t really address this issue. But there is still hope if you add the Trace flag –T3597 you can get identity column reseed to stop happening for a dedicated install of SQL Server but unfortunately this doesn’t work if you’re in SQL Azure.
How to test
USE madworks
GO
CREATE TABLE identitycheck (
id INT identity(1, 1)
,NAMES VARCHAR(100)
)
INSERT INTO identitycheck
SELECT 'Jayanth'
INSERT INTO identitycheck
SELECT 'Jayanth1'
INSERT INTO identitycheck
SELECT 'Jayanth2'
SELECT *
FROM identitycheck
SHUTDOWN
INSERT INTO identitycheck
SELECT 'Jayanth3'
INSERT INTO identitycheck
SELECT 'Jayanth4'
INSERT INTO identitycheck
SELECT 'Jayanth5'
SELECT *
FROM identitycheck
INSERT INTO identitycheck
SELECT 'Jayanth6'
INSERT INTO identitycheck
SELECT 'Jayanth7'
INSERT INTO identitycheck
SELECT 'Jayanth8'
SELECT *
FROM identitycheck
-- after end task
INSERT INTO identitycheck
SELECT 'Jayanth9'
INSERT INTO identitycheck
SELECT 'Jayanth10'
INSERT INTO identitycheck
SELECT 'Jayanth11'
SELECT *
FROM identitycheck
References
Please Consider Subscribing
