SEQUENCE BREAKS / GAP in Numbers after Restart SQL Server (gap between numbers after restarting server)

Leave a comment (1) Go to comments

A user reported that there is huge GAP between numbers generated by SEQUENCE after restarting the SQL Server.

SEQUENCE BREAKS / GAP in Numbers after Restart SQL Server (gap between numbers after restarting server) SQL Server Sequence SQL Server 2012

bit surprised, Don’t worry this is Expected behavior of SQL Server.

If you don’t know that Sequence like Identity is new feature of SQL Server 2012, please check my previouos post How to Create and use Sequence in SQL Server for details.

By default when you create a SEQUENCE you can either supply CACHE size. Caching is used to increase performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

But even if you don’t supply this parameter, SQL Server Automatically create a CACHE for SEQUENCE.


To fix this issue, you need to make sure, you add a NO CACHE option in sequence creation / properties like this.

CREATE SEQUENCE TEST_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE

Here is step by step by re production on issue for gap between numbers after restarting server and solution

 

EOF - SEQUENCE BREAKS / GAP in Numbers after Restart SQL Server (gap between numbers after restarting server), SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. Hi,

    very nicely explained the problem and the solution in the video great and thanx

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.