If you’re getting an error that reads something like “The requested range for sequence object ‘Sequence1’ exceeds the maximum or minimum limit. Retry with a smaller range.” in SQL Server, it’s probably because you’re trying to return a value that’s outside the range of the sequence object.

This can happen when you’re using the sp_sequence_get_range stored procedure to return a range from a sequence object that exceeds the minimum or maximum value for that sequence object, and the sequence object does not have CYCLE defined. It’s a similar error to error 11728, which can happen when using NEXT VALUE FOR to generate a new value from a sequence.

To fix this issue, either use a range that fits within the range of the sequence object, or change the sequence object to CYCLE.

Example of Error

Suppose we create the following sequence object:

CREATE SEQUENCE Sequence1
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 1000
    NO CYCLE;

This sequence is set to NO CYCLE, which means that it won’t cycle back to the MINVALUE after it reaches MAXVALUE. Instead, it will return the error mentioned above.

So let’s pass our sequence to the sp_sequence_get_range stored procedure:

DECLARE 
    @FirstValue sql_variant,
    @LastValue sql_variant,
    @CycleCount int,
    @Incr sql_variant,
    @MinValue sql_variant,
    @MaxValue sql_variant;
  
EXEC sys.sp_sequence_get_range
    @sequence_name = N'dbo.Sequence1',
    @range_size = 600,
    @range_first_value = @FirstValue OUTPUT,
    @range_last_value = @LastValue OUTPUT,
    @range_cycle_count = @CycleCount OUTPUT,
    @sequence_increment = @Incr OUTPUT,
    @sequence_min_value = @MinValue OUTPUT,
    @sequence_max_value = @MaxValue OUTPUT;
  
SELECT 
    @FirstValue AS FirstValue,
    @LastValue AS LastValue,
    @CycleCount AS CycleCount,
    @Incr AS Increment,
    @MinValue AS MinValue,
    @MaxValue AS MaxValue;

Result:

FirstValue  LastValue  CycleCount  Increment  MinValue  MaxValue
----------  ---------  ----------  ---------  --------  --------
1           600        0           1          1         1000    

So far, so good. This is what we’d normally expect from the sp_sequence_get_range stored procedure. We specified a @range_size of 600, which is reflected in the results with the difference between the FirstValue and LastValue columns.

However, if we run the same code again, we end up with the following error:

Msg 11732, Level 16, State 1, Procedure sys.sp_sequence_get_range_internal, Line 1
The requested range for sequence object 'Sequence1' exceeds the maximum or minimum limit. Retry with a smaller range.

We get this error because we exceeded the sequence’s MAXVALUE of 1000. This is because our @range_size of 600 would have resulted in a LastValue of 1200, which is outside the range of our sequence object (which has a range of 1000).

Solution 1

One way to fix this issue is to change our @range_size to a smaller range:

DECLARE 
    @FirstValue sql_variant,
    @LastValue sql_variant,
    @CycleCount int,
    @Incr sql_variant,
    @MinValue sql_variant,
    @MaxValue sql_variant;
  
EXEC sys.sp_sequence_get_range
    @sequence_name = N'dbo.Sequence1',
    @range_size = 200,
    @range_first_value = @FirstValue OUTPUT,
    @range_last_value = @LastValue OUTPUT,
    @range_cycle_count = @CycleCount OUTPUT,
    @sequence_increment = @Incr OUTPUT,
    @sequence_min_value = @MinValue OUTPUT,
    @sequence_max_value = @MaxValue OUTPUT;
  
SELECT 
    @FirstValue AS FirstValue,
    @LastValue AS LastValue,
    @CycleCount AS CycleCount,
    @Incr AS Increment,
    @MinValue AS MinValue,
    @MaxValue AS MaxValue;

Result:

FirstValue  LastValue  CycleCount  Increment  MinValue  MaxValue
----------  ---------  ----------  ---------  --------  --------
601         800        0           1          1         1000    

Solution 2

Another way to fix it is to change the range of the sequence object:

ALTER SEQUENCE Sequence1 MAXVALUE 2000;

Now we can run the code with the original @range_size again without error:

DECLARE 
    @FirstValue sql_variant,
    @LastValue sql_variant,
    @CycleCount int,
    @Incr sql_variant,
    @MinValue sql_variant,
    @MaxValue sql_variant;
  
EXEC sys.sp_sequence_get_range
    @sequence_name = N'dbo.Sequence1',
    @range_size = 600,
    @range_first_value = @FirstValue OUTPUT,
    @range_last_value = @LastValue OUTPUT,
    @range_cycle_count = @CycleCount OUTPUT,
    @sequence_increment = @Incr OUTPUT,
    @sequence_min_value = @MinValue OUTPUT,
    @sequence_max_value = @MaxValue OUTPUT;
  
SELECT 
    @FirstValue AS FirstValue,
    @LastValue AS LastValue,
    @CycleCount AS CycleCount,
    @Incr AS Increment,
    @MinValue AS MinValue,
    @MaxValue AS MaxValue;

Result:

FirstValue  LastValue  CycleCount  Increment  MinValue  MaxValue
----------  ---------  ----------  ---------  --------  --------
801         1400       0           1          1         2000    

Solution 3

Another way to do eliminate the error is to change the sequence object to a cycling sequence. While this likely won’t be appropriate in many cases, it’s still an option:

ALTER SEQUENCE Sequence1 CYCLE;

Now we can exceed the MAXVALUE without error, because it will cycle back through the range each time the end of the range has been reached

Let’s increase the @range_size and run the stored procedure again:

DECLARE 
    @FirstValue sql_variant,
    @LastValue sql_variant,
    @CycleCount int,
    @Incr sql_variant,
    @MinValue sql_variant,
    @MaxValue sql_variant;
  
EXEC sys.sp_sequence_get_range
    @sequence_name = N'dbo.Sequence1',
    @range_size = 800,
    @range_first_value = @FirstValue OUTPUT,
    @range_last_value = @LastValue OUTPUT,
    @range_cycle_count = @CycleCount OUTPUT,
    @sequence_increment = @Incr OUTPUT,
    @sequence_min_value = @MinValue OUTPUT,
    @sequence_max_value = @MaxValue OUTPUT;
  
SELECT 
    @FirstValue AS FirstValue,
    @LastValue AS LastValue,
    @CycleCount AS CycleCount,
    @Incr AS Increment,
    @MinValue AS MinValue,
    @MaxValue AS MaxValue;

Result:

FirstValue  LastValue  CycleCount  Increment  MinValue  MaxValue
----------  ---------  ----------  ---------  --------  --------
1401        200        1           1          1         2000    

This time it cycled back to the start of the sequence’s range and continued until the 800 range size was met.