My Sequences were reset

Question

My Sequences were reset

mar.ko

Default port

Points: 1451
More actions
August 25, 2015 at 4:41 pm

#311105

Somehow all of my sequences got reset to 1. The only thing that occured was the database was renamed.
SQL Server 2014
Is this a bug ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

I don't see any indications in the documentation that the ALTER DATABASE command would reset sequences. What process or syntax did you use to rename the database?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mar.ko Default port Points: 1451 More actions · Answer 2

mar.ko

Default port

Points: 1451

August 26, 2015 at 7:12 am

#1822454

I'm not sure....I didn't do it.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

mar.ko (8/26/2015)
I'm not sure....I didn't do it.

Ah... well, then other stuff may have been run. It's hard to say without knowing more. I ran this test:

USE TestDB;

GO

CREATE SEQUENCE dbo.MySequence

START WITH 1

INCREMENT BY 1;

CREATE TABLE dbo.TestSequence (

TestSequenceID INT DEFAULT (NEXT VALUE FOR dbo.MySequence),

MyVal VARCHAR(50)

);

GO

INSERT dbo.TestSequence

(TestSequenceID, MyVal)

VALUES (DEFAULT, 'SomeValue'),

(DEFAULT, 'SomeValue2'),

(DEFAULT, 'SomeOtherValue');

SELECT *

FROM dbo.TestSequence AS ts;

GO

USE master;

GO

ALTER DATABASE TestDB MODIFY NAME = NewTestDB;

GO

USE NewTestDB;

GO

INSERT dbo.TestSequence

(TestSequenceID, MyVal)

VALUES (DEFAULT, 'SomeValue');

SELECT *

FROM dbo.TestSequence AS ts;

The final result was 4, as you'd expect. It didn't reset because of the ALTER DATABASE command. I'm not sure what else may have happened.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mar.ko Default port Points: 1451 More actions · Answer 4

Thanks Grant...now I wonder if "someone" sabotaged me.

I created a stored proc to reset them to one plus the highest value in the related tables.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

mar.ko (8/26/2015)
Thanks Grant...now I wonder if "someone" sabotaged me.
I created a stored proc to reset them to one plus the highest value in the related tables.

Yeah, and no way to know unless you're capturing query metrics on the server 24/7. Sorry.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning