video

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!

Kevin3NF

My Pluralsight course for new DBAs

Follow @Dallas_DBAs