SQL Server Portal

The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated is one of the new error messages came in SQL Server 2012. This error message is related to Sequence object, a new database feature shipped with SQL Server 2012.

Let’s discuss this in detail:
Message Number: 11728

Severity : 16

Error Message: The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Ticket]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2
NO CACHE
GO
Select NEXT VALUE FOR [Ticket]
GO 3

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

Ooopps…… I am unable to execute it.

Resolution:
There are three different methods to resolve this error.

Method 1:
In method 1, you need to RESTART the sequence and it will be resolved but temporarily. Once it reaches to its maximum, it will give you the same error again.

ALTER SEQUENCE [dbo].[Ticket]
RESTART WITH 1
GO

Method 2:
In method 2, you need to INCREASE the sequence object  maximum number (If required by business logic) but again, it will be resolved but temporarily because once it reaches to its maximum, it will give you the same error again.

ALTER SEQUENCE [dbo].[Ticket]
MAXVALUE 10000
GO

Method 3:
In method 3, you need to CYCLE the sequence object (If required by business logic) and it will never give you this error message. But again it depends on your business logic because if you follow method 3 it will duplicate your sequence numbers.

ALTER SEQUENCE [dbo].[Ticket]
CYCLE
GO

Conclusion :
You can fix this error message by using above three different methods but before selecting any method be certain of your business logic.