SEQUENCE issuing numbers more than once?

This is all on

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
Oct 19 2012 13:38:57 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Running on Server 2008 Standard as a VMWare instance

I have an ETL load process that requires me to merge data form 4 sources. To do this we load data incrementally from each of the 4 instances into a STAGING table then we load it onwards into the destination table. During the load into the STAGING table we issue a unique RowID value for each loaded row based on a SEQUENCE attached to the STAGING table which is built using a script like this

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[STAGING].[Fact_Bert]') AND type in (N'U'))
DROP TABLE [STAGING].[Fact_Bert]
GO
IF  EXISTS (SELECT * FROM sys.sequences WHERE name = N'Bert_SEQ')
DROP SEQUENCE [FACT].[Bert_SEQ]
GO
IF NOT EXISTS (SELECT * FROM sys.sequences WHERE name = N'Bert_SEQ')
BEGIN
CREATE SEQUENCE [FACT].[Bert_SEQ] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE -2147483648
 MAXVALUE 2147483647
 CYCLE 
 CACHE  100 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [STAGING].[Fact_Bert](
[source_system_sk] [int] NOT NULL,
[RowID] [int] NOT NULL,
... other cols for data
CONSTRAINT [PK_STAGING_Bert] PRIMARY KEY CLUSTERED 
(
[source_system_sk] ASC,
... other PKEY cols form source system
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [STAGING]
) ON [STAGING]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FACT].[DF_Bert_RowID') AND type = 'D')
BEGIN
ALTER TABLE [STAGING].[Fact_Bert] ADD  DEFAULT (NEXT VALUE FOR [FACT].[Bert_SEQ]) FOR [RowID]
END
GO

So each row in STAGING ends up with a unique RowID, we carry these forwards into the LIVE tables and RowID is used as the PKEY on the LIVE tables.

This should be all well and good.

Every day a SQL Agent job runs a SSIS package that loads data into STAGING (after truncating it) and then moves it into LIVE.

The problem is that for some of the STAGING tables every time we run the daily load jobs we find that it re-allocates SEQUENCE values. So when we try and move data into the LIVE tables we get a PKEY error. Looking at the data before we run a load we can see that there are values in the STAGING table that run to 2700010 but if I look at the sys.sequences DMV the current_value for the sequence concerned is 2700000 and

SELECT NEXT VALUE FOR [FACT].[Bert_SEQ]

-- returns 2700008

So when we run the package we end up with values in STAGING that already exist in the LIVE table and hey presto a PKEY violation when we try and move the data into LIVE

I've got an SP that I can run on the table that will look at the sequence and reset it to start at a new value if the currently used value is higher than the current value returned by the DMV (it pads the values by 1000) I had to write this as manually figuring it out was taking forever

The server isn't usually restarted between loads, though I have done that deliberately today between load issues and it seems to make no difference.

The SEQUENCES are CACHED but the CACHE size is 100 and the overlaps seem pretty randomly sized. Though the more data I load the bigger the overlap seems to be. This makes me think that something is amiss in the way data is loaded by the OLEDB Destination in the SSIS packages that populate the STAGING tables. I'm not using transactions anywhere nor am I using SSIS transaction/rollback capability

I'm going to try switching the SEQUENCES to NO CACHE to see if that fixes the issue, though I don't like the perf hit I'm going to take as we're loading many tens of thousands running to hundreds of thousands of rows a day across 30+ tables. I appreciate this isn't exactly big but its big enough to be a problem.

Has anyone got an ideas/advice?

many thanks

Steve

January 22nd, 2014 5:46am

My gut reaction when I read this is that some rows are added without using default, but using an explicit value. The cache should have nothing to do with it. The effect of the cache would be the reverse: you cvuld get gaps.

But I know that there have been bugs around sequences, and you may want to look that the fixlists for the Cumulative Updates have been issued since SP1 was released, and apply that CU if you see something matching.

Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2014 5:23pm

FYI - I've reported the exact same problem on Connect, quite some time ago:  http://connect.microsoft.com/SQLServer/feedback/details/790161/next-value-for-sequence-can-fail-to-return-proper-next-value
January 22nd, 2014 8:38pm

I see that Steve has added a comment with a reference to this thread, suggestion that this would be a repro. I am afraid that does not count. I don't know if you have worked with software support, but if you have, you know that there is a limit on much time you can spend on reproducing an error from a vague description.

I repeat my recommendation to investigate the cumulative updates that has been released. If the problem is not resolved by applying the latest CU, I recommend that you open a case with Microsoft if you need to it to be solved. I suspect also, that if you are able to produce a repro that is with your current data and database, which would be far too large to submit on Connect.

Free Windows Admin Tool Kit Click here and download it now
January 23rd, 2014 2:12am

I have built a small DB and SSIS package that reproduce the issue reliably and have been testing out the scenarios where it fails.

Interestingly it seems this is only failing on 2 environments nth have the build number mentioned at the top of the post.

The other environments seem to have 

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 

and do not exhibit the same behaviour. So it would seem this was fixed between these two builds and it is a legacy issue of the mixed platforms we have.

I will update the connect item with this info as well.

January 24th, 2014 4:00am

That sounds promising. Thanks for reporting back!

Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 6:25am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics