Given this table (and these gaps):
CREATE TABLE dbo.TableWithGaps(id int IDENTITY(1,1), x char(1));
INSERT dbo.TableWithGaps(x) SELECT TOP (50000) 'x'
FROM sys.all_columns AS c
CROSS APPLY (SELECT TOP (100) * FROM sys.all_objects) AS o;
DELETE dbo.TableWithGaps WHERE id BETWEEN 11 AND 1000; -- 990
DELETE dbo.TableWithGaps WHERE id BETWEEN 10001 AND 17000; -- 7,000
DELETE dbo.TableWithGaps WHERE id BETWEEN 26001 AND 44000; -- 18,000
You can identify gaps as any range where the next identity column value -
LEAD(col) OVER (ORDER BY col) - is greater than 1. From there, we can identify the size of the gap by subtracting the start of the gap from the end, and order by that descending, to give us all the gaps, starting at the biggest one.
;WITH gaps AS
SELECT id, next_id = LEAD(id, 1) OVER (ORDER BY id)
SELECT gap_start = id + 1,
gap_end = next_id - 1,
gap_size = 1 + (next_id - 1) - (id + 1)
WHERE next_id - id > 1
ORDER BY gap_size DESC;
gap_start | gap_end | gap_size
--------- | ------- | --------
26001 | 44000 | 18000
10001 | 17000 | 7000
11 | 1000 | 990
Why would you reseed to be inside the biggest gap? Eventually (at some largely unpredictable future time), you'll reach the other side of that gap, and then failures happen. This is preventable. Just switch to
bigint and stop worrying about using every last slot that exists today. One of the reasons gaps are possible in identity columns - and why SQL Server doesn't try to reuse missing values - is that this is the most efficient way to do it. Trying to identify and fill in those gaps is expensive.