SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there any database setting that affects CHECKIDENT(RESEED)


Is there any database setting that affects CHECKIDENT(RESEED)

Author
Message
rwitt 95744
rwitt 95744
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 149
Greetings, all.
I am using CHECKIDENT(RESEED) in a Visual Studio 2013 script that clears numerous test data tables & repopulates them afresh, whenever we deploy a revised build of the database project. The proper syntax (as per Microsoft) to restart the key-count at #1, is RESEED,1 for truncated tables & RESEED,0 for tables from which rows were deleted (due to foreign keys) rather than truncated.
When the TFSBuild process is run on my PC (SQL Server 2012), all behaves as expected. The same is true when another developer runs the build to his PC. However, when we run the (very same) build process to our Dev (network) server, the RESEED,0 populates those tables starting with key #0, rather than key #1. B/c some of the test data tables are related or populate specifically to certain key #'s, this causes problems for us. I have to continually change the SQL script mentioned, to use RESEED,1 for the deleted-from tables, to get the test data on the Dev server correct, then change it back for anyone else's build.
All SQL Servers appear to be running the same SQL Server version & service pack levels. Is there any setting, that anyone is aware of, that could cause such behavior? It is 100% consistent, every time the build process is run. (Or do you think this is somehow some setting related to Visual Studio?)
Thanks,
Randy
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8052 Visits: 7163
No, don't think any other settings affect that.

Is there maybe something in the process where that table is truncating rather than deleting? Perhaps a missing foreign key constraint causing the process to do something different on that instance?

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15398 Visits: 18614
Start by checking out this article Context Is Everything - Check Your Settings
Cool
rwitt 95744
rwitt 95744
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 149
Thanks. I installed the script you suggested, on both my PC & the network server. There were only three settings that were different, none of which seems relevant:

My PC = IsFullTextInstalled = 1, IsIntegratedSecurityOnly = 1, IsFullTextEnabled = 0
Network server = IsFullTextInstalled = 0, IsIntegratedSecurityOnly = 0, IsFullTextEnabled = 1
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15398 Visits: 18614
Quick question, is the recovery model the same and is there any difference in the connections, ie. isolation?
Cool
rwitt 95744
rwitt 95744
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 149
Recovery model for both is "Full". Not quite sure what you mean on the other setting.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15398 Visits: 18614
rwitt 95744 (5/7/2014)
Recovery model for both is "Full". Not quite sure what you mean on the other setting.


That is the transaction isolation level, configurable ie. within the connection parameters. Simplest way to check is to run
DBCC USEROPTIONS
GO


on each system and check the output.
Cool
rwitt 95744
rwitt 95744
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 149
Running the above command, all 13 rows of settings are identical between the two servers.
I'm starting to think the issue is not in SQL Server but that somehow the Visual Studio build process being used on the two servers isn't the same, even if theoretically it's supposed to be.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15398 Visits: 18614
Can you run the DBCC statement from Visual Studio?
Cool
rwitt 95744
rwitt 95744
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 149
I think placing this in my test data script will resolve the issue (or rather, cover it, for both situations):

DECLARE @DELETEDFROM_<tablename> bit = 1
IF (SELECT IDENT_CURRENT('<tablename>') = 1 AND (SELECT COUNT(*) FROM <tablename>Wink = 0
SET @DELETEDFROM_<tablename> = 0 -- New table, has never contained any rows
-- Need to run the above prior to clearing the table

-- Run this after clearing the table
IF @DELETEDFROM_<tablename> = 0
BEGIN
-- New table, has never contained rows; CHECKIDENT(RESEED) treats it as a TRUNCATEd table
DBCC CHECKIDENT ('<tablename>', reseed, 1)
END
ELSE
BEGIN
-- Table exists & was DELETEd FROM; need to use different RESEED value
DBCC CHECKIDENT ('<tablename>', reseed, 0) -- Needed b/c DELETEd rather than TRUNCATEd this table
END
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search