SQL Server – The sequence object has reached its minimum or maximum value – Error 11728

The SEQUENCE statement is introduced in SQL Server 2012.
When you create a new sequence object with NO CYCLE option (which is the default cycle option for new sequence objects), like in the next example:

CREATE SEQUENCE [dbo].[My_Sequence]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99
NO CYCLE
GO

...you will get the following error when the sequence reaches its minimum or maximum value:

Msg 11728, Level 16, State 1, Line 1
The sequence object 'My_Sequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

In the sequence properties window, you can see that the sequence is exhausted, it has reached its maximum value:

sequence_properties_exhausted

Sequence maximum value can be increased:

ALTER SEQUENCE [dbo].[My_Sequence]
MAXVALUE 199
GO

To manually RESTART the sequence, you can give a new starting value:

ALTER SEQUENCE [dbo].[My_Sequence]
RESTART WITH 5
GO

If you don't provide a value, the default is the original starting value:

ALTER SEQUENCE [dbo].[My_Sequence]
RESTART
GO

When the sequence reaches the maximum value, it will give the same error again.

If you want the sequence to restart automatically, it can be done with the CYCLE property:

ALTER SEQUENCE [dbo].[My_Sequence]
CYCLE
GO

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">