My Pluralsight course for new SQL Server DBAs
SQL Server may skip 1000 numbers on an Identity column if the server crashes. Here’s why:
Too long, didn’t watch version:
SQL Caches 1000 numbers at a time to boost insert performance. In a crash and recovery, those numbers are gone.
SQL 2016 and earlier – use instance-wide trace flag 272 to turn off this behavior (performance might suffer).
SQL 2017 and later – its now a database scoped config item:
use MyDB; go ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF GO
Video shows a walk-through of before and after each fix, plus a “Two guys walk into a bar” joke when I disappeared to troubleshoot a broken demo…
Thanks for reading and/or watching!
My Pluralsight course for new DBAs
Hi, Kevin. Thanks for nice video.
Some remarks about trace flag 272 on SQL Server 2012 version. I recommend enable this trace flag for this version because identity random gaps (sometimes it is 1000, sometimes it is 10000) happens not only if server crashes but also when it restarted in normal mode. More info here: https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Trace%20Flag.md#272 and here https://github.com/ktaranov/sqlserver-kit/blob/master/Errors/Identity_gap_sql_server_2012.sql
Thanks Konstantin…excellent links as well. –Kevin