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
