Missing IDENTITY values

Many times we'll find holes in the sequence of our IDENTITY values.  This can be caused by the normal deletion of records, or it can happen if an insert transaction failed to complete, and was rolled back.  This statement will help you identify the missing values within your  table's IDENTITY sequence:

  DECLARE @ID INT
  DECLARE @MaxID INT
  DECLARE @MissingIDs TABLE ([ID] INT )

  SELECT @MaxID = [ID] FROM dbo.YourTable


  SET @ID = 1
  WHILE @ID <= @MaxID
  BEGIN
     IF NOT EXISTS (
          SELECT 'X' FROM dbo.YourTable
          WHERE [ID] = @ID)


     INSERT INTO @MissingIDs ([ID] )
     VALUES ( @ID )


     SET @ID = @ID + 1
  END

Review them all, or maybe just return the MIN and the MAX of the missing IDs that have been found:

  SELECT [ID] FROM @MissingIDs 
  SELECT MIN(ID) MinAvailID FROM @MissingIDs
  SELECT MAX(ID)+1 MaxAvailID FROM dbo.YourTable