Feeds:
Posts
Comments

Posts Tagged ‘Msg 11728 Level 16 State 1’

SQL SERVER 2012 – How to check Sequence has reached its minimum or maximum value

Posted in Solutions, SQL SERVER, tagged , , , , , , on October 5, 2013| Leave a Comment »

Recently I came across an issue related to the sequence object (shipped in SQL Server 2012) because usually we generate the next ID from the sequence object without checking whether it has reached its limit and it cannot generate a new ID. Due to this it generates an error as well. Given below are the error details.

Msg 11728, Level 16, State 1, Line 1
The sequence object ‘Seq_Sample1’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. Sequence object maintain its minimum and maximum value by default.

So what is the solution ? But before proceeding with the solution let me create a sample.

SAMPLE :

--This script is compatible with SQL Server 2012 and above.
--Create a sequence object
USE [AdventureWorks2012]
GO
--DROP SEQUENCE [dbo].[Seq_Sample1]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample1]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
GO
--Generate sequence number up to 4 ID
--because it has a maximum limit of 4.
SELECT Next value for [dbo].[Seq_Sample1]
GO 4

SOLUTION 1 : (Not recommended)
In this solution, you need to check the minimum and maximum value of the sequence with the current value and if it matches either of them, it means that sequence has been reached to its minimum or value and you cannot generate a new ID. It is not recommended because it is a lengthy procedure. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @minimum_value As sql_variant
DECLARE @maximum_value As sql_variant
DECLARE @current_value As sql_variant

SELECT
@minimum_value=[minimum_value]
,@maximum_value=[maximum_value]
,@current_value=[current_value]
FROM
sys.sequences AS seq
WHERE name ='Seq_Sample1'

SELECT IIF(@minimum_value=@current_value
OR @maximum_value=@current_value
,'You cannot generate a new ID'
,'You can generate a new ID') AS [Result]
--OUTPUT

sequence limit.1.1

SOLUTION 2 : Using Is_exhausted property (Recommended)
In this solution, we will use a sequence property namely “is_exhausted” and can quickly check whether it reached its limit or not.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT IIF((SELECT is_exhausted FROM sys.sequences AS seq
WHERE name ='Seq_Sample1')=1
,'You cannot generate a new ID'
,'You can generate a new ID')
AS [Result]
--OUTPUT

sequence limit.1.1

CONCLUSION :
As you can see, both solutions generate the same output but solution 2 is recommended because we used a property namely “is_exhausted” and reduced the size of the script.

Read Full Post »

SQL SERVER 2012 – Fix – Error – 11728 – The sequence object has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Posted in Errors, SQL SERVER, tagged , , , , , on April 5, 2013| 2 Comments »

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.

Read Full Post »